Expand Auto Filter to Another Worksheet

[barim]

I have this VBA Code:

Sub RefreshFilter()
'
' RefreshFilter Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Range("A1").Select
    Range("A1:I1019277").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("A1:I3"), Unique:=False
End Sub
Sub ClearSearch()
'
' ClearSearch Macro
'
' Keyboard Shortcut: Ctrl+w
'
    ActiveSheet.ShowAllData
    Range("L5").Select
    Selection.ClearContents
    Range("L1").Select
    Selection.ClearContents
End Sub

I am running out of space in the first sheet and would like to continue on Sheet 2. I would like to see filtered data on Sheet 1.
Or maybe everything filtered on Sheet 3.

Thanks.

 

[SpillerBD]

Maybe you need to use the PowerQuery add-in instead...

 

[Joe4]

Another possibility...

If you have Microsoft Access, you can do it there. There is no limitation on row size, just database size, and Access is great for doing things like filters/queries.

 

[barim]

I know about Access queries, but I am stucked with Excel.

I named Sheet 1 data as "Database" by defining name. Sheet 2 I named "Database1".
Is there a way to include Range ("Database", "Database1)?
This way it can search the same criteria. I have columns name same on both sheets.

 

[Joe4]

It looks like you will want to go with SpillerBD's suggestion then.
See: https://support.office.com/en-us/ar...or-Excel-6E92E2F4-2079-4E1F-BAD5-89F6269CD605

 

[SpillerBD]

I know about Access queries, but I am stucked with Excel.

I named Sheet 1 data as "Database" by defining name. Sheet 2 I named "Database1".
Is there a way to include Range ("Database", "Database1)?
This way it can search the same criteria. I have columns name same on both sheets.

PowerQuery I think is the best answer, forgoing Access, but PowerQuery does require the appropriate version of Office/Excel. If you cannot load the add-in, then there is a possibility of using PivotTable with Multiple Consolidation ranges. I don't think PivotTable is as efficient as PowerQuery so I hope you are running 64-bit, a screaming processor and plenty of RAM. Definitely use the "Defer Layout Update" option if attempting that.
And then with results possibly exceeding the row limit.... I don't know what kind of crash that would cause. You may wish to try in a copy of your WorkBook..