Hi guys,
Just had a question on how to filter this correctly.
On my dahsboard tab, I have a bunch of criteria that the user can input.
On my panorama tab, is the dataset that needs to be filter according to the the range on the dashboard tab.
I have have it set so that when it filters, it would open a new workbook and ask the user to save. that is working but I noticed that after the filter, it gives me a lot less data then if I manually filter. I guess Advanced Filter works only a single row for the criteria?
the number of criteria will vary, example, usually the location will be 1 single place, while the FCLM Area and Shift Pattern will always be different with no set amount.
Any help would be much appreciated.
Just had a question on how to filter this correctly.
On my dahsboard tab, I have a bunch of criteria that the user can input.
On my panorama tab, is the dataset that needs to be filter according to the the range on the dashboard tab.
Quebec Callem All.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | |||
4 | Location: Location Name | FCLM Area | Shift Pattern | Department | Payroll Employee Status | Employee Type | ||
5 | YUL2 | 13 | NA | 1299030 | Active | Hourly | ||
6 | DA | |||||||
7 | DL | |||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
Dashboard |
Quebec Callem All.xlsb | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Location: Location Name | Employee Id | Login | First Name | Last Name | Payroll Employee Status | Shift Pattern | Department | FCLM Area | Employee Type | Last Hire Date | Mobile Phone - Private | ||
2 | ||||||||||||||
3 | ||||||||||||||
4 | ||||||||||||||
Panorama |
VBA Code:
Sub filtercopy()
Dim cwb As Workbook, nwb As Workbook
Dim rgData As Range, rgCriteria As Range, rgOutput As Range
Set cwb = ThisWorkbook
Set rgData = cwb.Worksheets("Panorama").Range("A2").CurrentRegion
Set rgCriteria = cwb.Worksheets("Dashboard").Range("H5").CurrentRegion
Workbooks.Add
Set nwb = ActiveWorkbook
Set rgOutput = nwb.Worksheets("Sheet1").Range("A1")
rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
Call sbSaveExcelDialog
nwb.Close
End Sub
VBA Code:
Sub sbSaveExcelDialog()
Dim Fldr As String, fname As String
With Application.FileDialog(4)
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
Fldr = .SelectedItems(1)
End With
fname = ThisWorkbook.Worksheets("Dashboard").Range("C11")
ActiveWorkbook.SaveAs Filename:=Fldr & "\" & fname & ".xls", FileFormat:=xlNormal
End Sub
I have have it set so that when it filters, it would open a new workbook and ask the user to save. that is working but I noticed that after the filter, it gives me a lot less data then if I manually filter. I guess Advanced Filter works only a single row for the criteria?
the number of criteria will vary, example, usually the location will be 1 single place, while the FCLM Area and Shift Pattern will always be different with no set amount.
Any help would be much appreciated.
Last edited: