Excel VBA: Filter with multiple criteria ranges

[gd6noob]

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.

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

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

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.

 

[AlphaFrog]

In an Advanced Filter Criteria Table, each criteria in a single row is an AND. Each row is an OR.

In your current table, only the first row has a location, area, and department. In the other rows, those same criteria are blank. They need to be repeated.

Location: Location Name	FCLM Area	Shift Pattern	Department	Payroll Employee Status	Employee Type
YUL2	13	NA	1299030​	Active	Hourly
YUL2	13	DA	1299030​	Active	Hourly
YUL2	13	DL	1299030​	Active	Hourly

 

[gd6noob]

In an Advanced Filter Criteria Table, each criteria in a single row is an AND. Each row is an OR.

In your current table, only the first row has a location, area, and department. In the other rows, those same criteria are blank. They need to be repeated.

Location: Location Name	FCLM Area	Shift Pattern	Department	Payroll Employee Status	Employee Type
YUL2	13	NA	1299030​	Active	Hourly
YUL2	13	DA	1299030​	Active	Hourly
YUL2	13	DL	1299030​	Active	Hourly

ohh.. then using advanced filter isnt the way to go, because within each department, they will have a different FCLM area # and a different shift pattern. I would need to have 1 line for each to cover all the variables..

Using autofilter is the best option? if not, whats the best option?

 

[AlphaFrog]

IF you want all FCLM Areas and all Shift Patterns for a given Department, then just leave them blank.

Autofilter may be easier. I don't fully appreciate the nature of your data and what you are filtering for.