[Solved] General Question About Filtering

B

bino1121

New member
Currently in the thinking stage right now but I am wondering if in excel VBA I have a report that comes in. This report then needs to be filtered by specific set of buildings. I just learned that you can add macros to the ribbon at the top of excel. INSTEAD of having a ton of buttons on the ribbon to select a specific set of buildings (buildings set A, Buildings Set B, Building Set C....Etc) is there a way that I could have it so I click a button to begin generating a report and it prompts the user to select a list of buildings.....

if that's confusing I guess an example would be the user clicks the button on the ribbon to run the report. A window pops prompting the user to select which set of buildings they would like to use (A, B, C, D....Etc) then the report runs and filters based off of the users prompted selection. If That makes sense??

I have a sheet that is a "library" of buildings if you will. This library contains multiple sheets each with different specific buildings on them. SO Ideally the user would be prompted to select the specific list they want to use before the actual macro runs. Because then each list is used to filter a master list down to those specific buildings.
 
Last edited:
bino1121 said:
Currently in the thinking stage right now but I am wondering if in excel VBA I have a report that comes in. This report then needs to be filtered by specific set of buildings. I just learned that you can add macros to the ribbon at the top of excel. INSTEAD of having a ton of buttons on the ribbon to select a specific set of buildings (buildings set A, Buildings Set B, Building Set C....Etc) is there a way that I could have it so I click a button to begin generating a report and it prompts the user to select a list of buildings.....

if that's confusing I guess an example would be the user clicks the button on the ribbon to run the report. A window pops prompting the user to select which set of buildings they would like to use (A, B, C, D....Etc) then the report runs and filters based off of the users prompted selection. If That makes sense??

I have a sheet that is a "library" of buildings if you will. This library contains multiple sheets each with different specific buildings on them. SO Ideally the user would be prompted to select the specific list they want to use before the actual macro runs. Because then each list is used to filter a master list down to those specific buildings.
Click to expand...
Dear Bino1121,

I saw your post about your Excel VBA report filtering issue. Based on your post, I have designed two user forms named frmBuildingList and frmReports. I also created the necessary procedures to prompt users to select a specific list of buildings before running the report. This solution will lead you to make the report-generation process more efficient, and you only need to modify the design and code as needed. Here I had to create a customized tab titled Filter And Report, and inside that tab, I took a group called Filter and Get Report. Later, I assigned the intended macro named BuildingPicker from the modBuildingPicker module. To better understand, I am going to provide a link to a video.

Steps:
  • You must keep your building name in the Dataset worksheet for this demonstration.
  • Later you have to go to the Filter And Report tab, and from the Filter And Get Report group click on the Building Picker icon.
  • Next, the Building Picker window will appear. Choose your desired building names and close the window.
  • Afterwards, another window called Report will appear and show the selected building name. From that window, click on the Get Report button and then click on Close.
  • As a result, the reports will store in another newly created sheet.
(VIDEO)​
frmBuildingList Code:

Code: 
Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim dataRange As Range
    Dim cell As Range
 
    lastRow = Sheets("Dataset").Cells(Rows.count, 1).End(xlUp).Row
 
    Set dataRange = Sheets("Dataset").Range("A2:A" & lastRow)
 
    With Me.lstBuildingList
        .ColumnCount = 3
        .ColumnHeads = True
        .ColumnWidths = "75, 75, 75"
        .RowSource = "Dataset!A2:C" & lastRow
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 
    Call addingItems
 
    frmReport.Show
 
End Sub

Sub addingItems()
 
    Dim selectedItem As String
    Dim i As Integer
 
    For i = 0 To lstBuildingList.ListCount - 1
        If lstBuildingList.Selected(i) Then
            selectedItem = lstBuildingList.List(i)
            frmReport.lstReport.AddItem selectedItem
        End If
    Next i
 
End Sub

frmReports Code:
Code: 
Private Sub btnClose_Click()
 
    Unload frmReport
 
End Sub

Private Sub btnGetReport_Click()
 
    Call FilterAndExport
 
End Sub

Sub FilterAndExport()

    Dim i, j, lastRow, lastRow1 As Integer
    Dim newSheet As Worksheet

 
    lastRow = Sheets("Dataset").Cells(Rows.count, 1).End(xlUp).Row
 
    Set newSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
 
    ThisWorkbook.Sheets("Dataset").Range("A1:C1").Copy Destination:=newSheet.Range("A1")
 
    lastRow1 = newSheet.Cells(Rows.count, 1).End(xlUp).Row + 1
 
    For i = 0 To lstReport.ListCount - 1
 
        For j = 2 To lastRow
 
  
            If Sheets("Dataset").Range("A" & j).Value = lstReport.List(i) Then
      
                ThisWorkbook.Sheets("Dataset").Range("A" & j & ":C" & j).Copy Destination:=newSheet.Range("A" & lastRow1)
                lastRow1 = lastRow1 + 1
      
            End If
 
        Next j
 
    Next i
 
    Columns.AutoFit
 
End Sub

modBuildingPicker:
Code: 
Sub BuildingPicker()
 
    frmBuildingPicker.Show
 
End Sub

I am giving you the Workbook used to explore your issue. Feel free to inform us if you face any other issues.

Best regards,
Lutfor Rahman Shimanto
 

Attachments

  • Bino1121Solved.xlsm
    28 KB · Views: 3
Last edited:
Lutfor Rahman Shimanto said:
Dear Bino1121,

I saw your post about your Excel VBA report filtering issue. Based on your post, I have designed two user forms named frmBuildingList and frmReports. I also created the necessary procedures to prompt users to select a specific list of buildings before running the report. This solution will lead you to make the report-generation process more efficient, and you only need to modify the design and code as needed. Here I had to create a customized tab titled Filter And Report, and inside that tab, I took a group called Filter and Get Report. Later, I assigned the intended macro named BuildingPicker from the modBuildingPicker module. To better understand, I am going to provide a link to a video.

Steps:
  • You must keep your building name in the Dataset worksheet for this demonstration.
  • Later you have to go to the Filter And Report tab, and from the Filter And Get Report group click on the Building Picker icon.
  • Next, the Building Picker window will appear. Choose your desired building names and close the window.
  • Afterwards, another window called Report will appear and show the selected building name. From that window, click on the Get Report button and then click on Close.
  • As a result, the reports will store in another newly created sheet.
(VIDEO)​
frmBuildingList Code:

Code: 
Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim dataRange As Range
    Dim cell As Range
 
    lastRow = Sheets("Dataset").Cells(Rows.count, 1).End(xlUp).Row
 
    Set dataRange = Sheets("Dataset").Range("A2:A" & lastRow)
 
    With Me.lstBuildingList
        .ColumnCount = 3
        .ColumnHeads = True
        .ColumnWidths = "75, 75, 75"
        .RowSource = "Dataset!A2:C" & lastRow
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 
    Call addingItems
 
    frmReport.Show
 
End Sub

Sub addingItems()
 
    Dim selectedItem As String
    Dim i As Integer
 
    For i = 0 To lstBuildingList.ListCount - 1
        If lstBuildingList.Selected(i) Then
            selectedItem = lstBuildingList.List(i)
            frmReport.lstReport.AddItem selectedItem
        End If
    Next i
 
End Sub

frmReports Code:
Code: 
Private Sub btnClose_Click()
 
    Unload frmReport
 
End Sub

Private Sub btnGetReport_Click()
 
    Call FilterAndExport
 
End Sub

Sub FilterAndExport()

    Dim i, j, lastRow, lastRow1 As Integer
    Dim newSheet As Worksheet

 
    lastRow = Sheets("Dataset").Cells(Rows.count, 1).End(xlUp).Row
 
    Set newSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
 
    ThisWorkbook.Sheets("Dataset").Range("A1:C1").Copy Destination:=newSheet.Range("A1")
 
    lastRow1 = newSheet.Cells(Rows.count, 1).End(xlUp).Row + 1
 
    For i = 0 To lstReport.ListCount - 1
 
        For j = 2 To lastRow
 
 
            If Sheets("Dataset").Range("A" & j).Value = lstReport.List(i) Then
     
                ThisWorkbook.Sheets("Dataset").Range("A" & j & ":C" & j).Copy Destination:=newSheet.Range("A" & lastRow1)
                lastRow1 = lastRow1 + 1
     
            End If
 
        Next j
 
    Next i
 
    Columns.AutoFit
 
End Sub

modBuildingPicker:
Code: 
Sub BuildingPicker()
 
    frmBuildingPicker.Show
 
End Sub

I am giving you the Workbook used to explore your issue. Feel free to inform us if you face any other issues.

Best regards,
Lutfor Rahman Shimanto
Click to expand...
This is awesome thank you so much! I did have one question though. So how I have it set up is that the report is on Sheet1 and then there is a list of buildings on Sheet2. This list of buildings changes through another macro depending on the day. For instance, On Sheet2 I would have to select what day it is then the list of buildings for that day auto populates into the A column. Instead of having it to where I have to pick out each individual building I would like it to be where I can go to Sheet2 run the macro that says what day of the week is it? and then I select Monday for example and the list of buildings for Monday fills in.

The problem with selecting each on individually is that some of the lists of buildings are long and also change constantly. So I have a library workbook that gets updated frequently. Then I have my report workbook that has 2 sheets. Sheet1 is where all the data gets imported and filtered. Sheet2 is where I import the buildings based on what day of the week it is. Right now I have it set up where I have to go into the macro page to find what day of the week I need and then run it. What I would like is that the box you have show up where you select the buildings is for days of the week and then based on the selection that imports the list from the library into sheet2 which are then used to filter the dataset on sheet1 I have the majority of that figured out I just am trying to get the day of the week box to show up if that makes sense.
 
Hello Guys,

Anyone who knows how to enabled macros VBA

Received message that says that Microsoft has disabled my macros even if i can try to enable them i still see the same error message. Please help
 
Jasper.Muduvhadzi said:
Hello Guys,

Anyone who knows how to enabled macros VBA

Received message that says that Microsoft has disabled my macros even if i can try to enable them i still see the same error message. Please help
Click to expand...
Hello Jasper Muduvhadzi,

Thanks for reaching out on our platform. To solve the issue you are addressing, you can select the file and press Alt+Enter. A window will pop up, and next, apply the following.
  • Choose the General tab.
  • Check the Unblock option.
  • Click on the OK button.
  • Then open the intended file.
Jasper Muduvhadzi.png
​
If necessary, enable the macros after that. Hopefully, it will not change and solve the issue. Good luck.

Regards
Lutfor Rahman Shimanto
 
Lutfor Rahman Shimanto said:
Hello Jasper Muduvhadzi,

Thanks for reaching out on our platform. To solve the issue you are addressing, you can select the file and press Alt+Enter. A window will pop up, and next, apply the following.
  • Choose the General tab.
  • Check the Unblock option.
  • Click on the OK button.
  • Then open the intended file.
If necessary, enable the macros after that. Hopefully, it will not change and solve the issue. Good luck.

Regards
Lutfor Rahman Shimanto
Click to expand...
Thats is the problem, my property does not have unblock box
 
Jasper.Muduvhadzi said:
Thats is the problem, my property does not have unblock box
Click to expand...
Hello Jasper Makhadzi,

You can follow the below guidance to resolve your issue.

  • Open the intended Excel where you want to enable macros.
  • Click on the File menu in the top left corner, and then click on Options.
  • From the left-hand menu, click on Trust Center.
  • Click on the Trust Center Settings button.
  • In the left-hand menu, click on Macro Settings.
  • Select Enable all macros (or Enable macros for all Office applications, depending on your version).
  • Click OK to close the dialog box.
  • Restart the Office application.

Giving any prominent solution to issues like yours is difficult without seeing the Workbook. If possible, you can share the workbook to understand your problem better.

Regards
Lutfor Rahman Shimanto
 
bino1121 said:
This is awesome thank you so much! I did have one question though. So how I have it set up is that the report is on Sheet1 and then there is a list of buildings on Sheet2. This list of buildings changes through another macro depending on the day. For instance, On Sheet2 I would have to select what day it is then the list of buildings for that day auto populates into the A column. Instead of having it to where I have to pick out each individual building I would like it to be where I can go to Sheet2 run the macro that says what day of the week is it? and then I select Monday for example and the list of buildings for Monday fills in.

The problem with selecting each on individually is that some of the lists of buildings are long and also change constantly. So I have a library workbook that gets updated frequently. Then I have my report workbook that has 2 sheets. Sheet1 is where all the data gets imported and filtered. Sheet2 is where I import the buildings based on what day of the week it is. Right now I have it set up where I have to go into the macro page to find what day of the week I need and then run it. What I would like is that the box you have show up where you select the buildings is for days of the week and then based on the selection that imports the list from the library into sheet2 which are then used to filter the dataset on sheet1 I have the majority of that figured out I just am trying to get the day of the week box to show up if that makes sense.
Click to expand...
Dear Bino1121,

I hope this post finds you well. After exploring your long previous post, I noted some requirements you mentioned. As you requested, I came up with an advanced solution, and I am attaching the Workbook used to investigate the described issue.

For technical reasons, I am not allowed to give the code for how I can create a customized ribbon called ExcelDemy. You will find this ribbon only in this Workbook.

That's why I tried to fulfil all your requirements within this Workbook—the requirement I found from your previous posts.

Requirements:
  • Have a list of buildings on Sheet2 that changes depending on the day.
  • Able to select the day of the week on Sheet2 and have the corresponding list of buildings auto-populate into the A column.
  • The lists of buildings are long and change frequently, so we must import these from a Library Workbook.
  • Sheet1 is where all the data gets imported and filtered, while Sheet2 imports the buildings based on the day of the week.
  • Rather than going into the macro page to find the day of the week and run it, a box must be created to allow the user to select the day and import the corresponding list of buildings from the library into Sheet2.
Likewise, I have two user forms, but this time using the Workbook, you can select the path of the Library workbook. As well as you can print the desired data as a pdf file.

Workbook Events:

Code: 
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    
    If Sh.name = "Dataset" Then

        Sh.Copy after:=Sheets(Sheets.count)
        MsgBox "Rename the newly created sheet as Dataset."
        
    End If
    
End Sub

Private Sub Workbook_Open()
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets

        If Left(ws.name, 7) <> "Dataset" Then

            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        
        End If
        
    Next ws
    
End Sub

frmBuildingPicker:

Code: 
Private Sub CommandButton1_Click()
    
    Call FilterCopyPaste
    frmReport.Show
    
End Sub

Private Sub UserForm_Initialize()
    
    cmbDays.AddItem "Monday"
    cmbDays.AddItem "Tuesday"
    cmbDays.AddItem "Wednesday"
    cmbDays.AddItem "Thursday"
    cmbDays.AddItem "Friday"
    cmbDays.AddItem "Saturday"
    cmbDays.AddItem "Sunday"

End Sub


Sub FilterCopyPaste()
    
    On Error Resume Next
    Dim libWB As Workbook
    Dim repWB As Workbook
    Dim libWS As Worksheet
    Dim ws As Worksheet
    Dim repWS, newSheet As Worksheet
    Dim lastRow As Long
    Dim filterDays As String
    Dim libFilePath As Variant
    
    libFilePath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx*),*.xlsx*", Title:="Select Library Workbook")
    If libFilePath = False Then Exit Sub
    
    Set libWB = Workbooks.Open(libFilePath)
    Set libWS = libWB.Worksheets("Sheet1")
    Set newSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
    
    filterDays = frmBuildingPicker.cmbDays.Value
    If filterDays = "" Then Exit Sub
    
    ThisWorkbook.Sheets("Dataset").Cells.Clear
    libWS.UsedRange.Copy Destination:=ThisWorkbook.Sheets("Dataset").Range("A1")
    ThisWorkbook.Sheets("Dataset").Columns.AutoFit
    
    libWS.Range("A1:D1").AutoFilter Field:=2, Criteria1:=filterDays
    
    
    libWS.AutoFilter.Range.Copy Destination:=newSheet.Range("A1")
    newSheet.Columns.AutoFit
    
    For Each ws In ThisWorkbook.Worksheets

        If ws.name = filterDays & " Wise Building" Then

            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        
        End If
        
    Next ws
    
    newSheet.name = filterDays & " Wise Building"
    MsgBox "The Building names have been filtered."
    
    libWS.AutoFilterMode = False
    libWB.Close SaveChanges:=False
    
End Sub

frmReport:

Code: 
Private Sub btnClose_Click()
    
    Unload frmReport
    Unload frmBuildingPicker
    
End Sub

Private Sub CommandButton1_Click()
    
    Call displayDayWiseBuildingList
    
End Sub

Private Sub CommandButton2_Click()
    
    Call PrintReport
    
End Sub

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim lstRow As Long
    Dim lstCol As Long
    Dim rngData As Range
    
    Set ws = ThisWorkbook.Worksheets(CStr(frmBuildingPicker.cmbDays.Value) & " Wise Building")
    lstRow = ws.Cells(ws.Rows.count, 1).End(xlUp).Row
    lstCol = 4
    
    Set rngData = ws.Range("A2").Resize(lstRow, lstCol)
    
    With Me.lstReport
        .ColumnCount = lstCol
        .ColumnHeads = True
        .ColumnWidths = "80, 50, 50, 50"
        .RowSource = rngData.Address
    End With
    
    Me.TextBox1.Value = Application.WorksheetFunction.CountIfs(Range("D2:D" & lstRow), "True")
    
    Call UniqueLocationList
    Call UniqueBuildingList
    
End Sub


Sub UniqueLocationList()

    Dim rng As Range
    Dim lstRow As Long
    Dim ws As Worksheet
    Dim loc As String
    Dim uniqueLocs As String
    Dim count As Integer
    
    Set ws = ThisWorkbook.Worksheets(CStr(frmBuildingPicker.cmbDays.Value) & " Wise Building")
    lstRow = ws.Cells(ws.Rows.count, 1).End(xlUp).Row
    Set rng = Range("C2:C" & lstRow)
    
    For Each cell In rng
        If InStr(1, uniqueLocs, cell.Value) = 0 Then
            uniqueLocs = uniqueLocs & cell.Value & ","
            count = count + 1
        End If
    Next cell
    
    Me.TextBox2.Value = count
    
    uniqueLocs = Left(uniqueLocs, Len(uniqueLocs) - 1)
    MsgBox "The number of unique locations is: " & count & vbNewLine & "The unique locations are: " & uniqueLocs

End Sub

Sub UniqueBuildingList()

    Dim rng As Range
    Dim lstRow As Long
    Dim ws As Worksheet
    Dim Building As String
    Dim uniqueBuilding As String
    Dim count As Integer
    
    Set ws = ThisWorkbook.Worksheets(CStr(frmBuildingPicker.cmbDays.Value) & " Wise Building")
    lstRow = ws.Cells(ws.Rows.count, 1).End(xlUp).Row
    Set rng = Range("A2:A" & lstRow)
    
    For Each cell In rng
        If InStr(1, uniqueBuilding, cell.Value) = 0 Then
            uniqueBuilding = uniqueBuilding & cell.Value & ","
            count = count + 1
        End If
    Next cell
    
    Me.TextBox3.Value = count
    
    uniqueBuilding = Left(uniqueBuillding, Len(uniqueBuilding) - 1)
    MsgBox "The number of unique Buildings are: " & count & vbNewLine & "The unique Buildings are: " & uniqueBuilding

End Sub

Sub PrintReport()

    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets(CStr(frmBuildingPicker.cmbDays.Value) & " Wise Building")
    ws.PrintOut

End Sub

modBuildingPicker:

Code: 
Sub BuildingPicker(control As IRibbonControl)
    
    frmBuildingPicker.Show
    
End Sub

OUTPUT:


Thanks a ton for reaching out. I hope this will achieve your goal. To better understand, besides the Solution Workbook, I am also giving you the demo Library Workbook. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy Team
 

Attachments

  • Bino1121Solved.xlsm
    44 KB · Views: 0
  • Library.xlsx
    13.4 KB · Views: 0
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
2
Total visitors
2
Totals may include hidden visitors.

Forum statistics

Threads
292
Messages
1,268
Members
531
Latest member
lonkfps

Share this page

Top