advance filter with list and criteria in different sheets in the same workbook

[still learning]

Hi,

In an advance filter:
Is there a way to have sheet 1 have the list range
And sheet 2 have the criteria and extract.
OR does all three have to be in the same sheet.

I tried to have the list range (“DATA”) in sheet 1
And the criteria and extract in sheet 2 but I don’t get any results
I had to copy the range "DATA" to sheet 2 to make it work

For future…does all three have to be in the same workbook



mike

 

[kevin9999]

If you wanted to, you could even have the extract go to a third sheet. Consider the following example code:

Option Explicit
Sub Advanced_Filter_Example()
    Dim rngList As Range, rngCriteria As Range, rngCopyTo As Range
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
  
    Set rngList = ws1.Range("A1").CurrentRegion     '<< the data to filter is on sheet 1
    Set rngCriteria = ws2.Range("A1").CurrentRegion '<< the criteria is on sheet 2
    Set rngCopyTo = ws3.Range("A1")                 '<< the 'extract to' range is on sheet 3
  
    ws3.Range("A1").CurrentRegion.Clear
  
    rngList.AdvancedFilter xlFilterCopy, rngCriteria, rngCopyTo

End Sub

with this on sheet 1

Book1
	A	B	C
1	header1	header2	header3
2	a	data	data
3	b	data	data
4	c	data	data
5	a	data	data
6	b	data	data
7	c	data	data
8
Sheet1

and this on sheet 2

Book1
	A	B
1	header1
2	a
3
Sheet2

you'll get this on sheet 3

Book1
	A	B	C
1	header1	header2	header3
2	a	data	data
3	a	data	data
4
Sheet3

but as far as I know, they do have to be in the same workbook (but happy to be corrected)

 

[kevin9999]

Addendum:

I just tested it, and it turns out that all three can be on different workbooks as well! This code worked:

Option Explicit
Sub Advanced_Filter_Example_2()
    Dim rngList As Range, rngCriteria As Range, rngCopyTo As Range
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = Workbooks("filter book 2").Worksheets("Sheet1")
    Set ws3 = Workbooks("filter book 3").Worksheets("Sheet1")
    
    Set rngList = ws1.Range("A1").CurrentRegion     '<< the data to filter is on sheet 1
    Set rngCriteria = ws2.Range("A1").CurrentRegion '<< the criteria is on sheet 2
    Set rngCopyTo = ws3.Range("A1")                 '<< the 'extract to' range is on sheet 3
    
    ws3.Range("A1").CurrentRegion.Clear
    
    rngList.AdvancedFilter xlFilterCopy, rngCriteria, rngCopyTo

End Sub

this

filter book 1.xlsm
	A	B	C
1	header1	header2	header3
2	a	data	data
3	b	data	data
4	c	data	data
5	a	data	data
6	b	data	data
7	c	data	data
8
Sheet1

and this

filter book 2.xlsx
	A	B
1	header1
2	a
3
Sheet1

led to this

filter book 3.xlsx
	A	B	C
1	header1	header2	header3
2	a	data	data
3	a	data	data
4
Sheet1

 

[still learning]

Hi kevin9999
Thank you
It works perfect
Now I don't have to have extra columns that copies the "list" from sheet1 out of sight on sheet2 where the "extract" results is
I'm also putting your other macro in my "Macro Helper" workbook for the future.

mike

 

[kevin9999]

Hi kevin9999
Thank you
It works perfect
Now I don't have to have extra columns that copies the "list" from sheet1 out of sight on sheet2 where the "extract" results is
I'm also putting your other macro in my "Macro Helper" workbook for the future.

mike

You're welcome Mike, and thanks for the feedback.