Filter table inside Excel the fast way (using advanced filter) into another sheet.
This sub assumes all these sheets are presented in the same workbook.
ShSource, ShFilter and ShCopyTo.
Will have another version with more flexible options.
Used and worked perfect.
Make sure you understand AdvancedFilter in Excel to apply filter range, data range and copy to range. This sub uses Excel AdvancedFilter.
Trick to use ANmAdvancedFilter below
To filter large table on multiple columns at the same time having each column to be one of many values
Use .... multiple sheets to filter once per column
Like we did in Reiporter tool
Function ANmAdvancedFilter(ShSource, RSource, ShFilter, RFilter, ShCopyTo, Optional RCopyTo = "A1", Optional WB = "This")
' Copy table from a sheet into another after applying filter and returns number of rows
' aka AdvancedFilter
' +++ For now, all should be within same workbook +++
' RSource = Range of source, full range
' ShSource= Sheet name of source table
' RFilter = Range having filter, 1st row should have similar values from RSource
' ShFilter= sheet having RFilter
' RCopyTo = Range to copy filtered output to, expect to have entire sheet cleared and filled again
' ShCopyTo= Sheet to copy to
'
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
Workbooks(WB).Worksheets(ShSource).Range(RSource).AdvancedFilter xlFilterCopy, Workbooks(WB).Worksheets(ShFilter).Range(RFilter), Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo), False
ANmAdvancedFilter = Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo).CurrentRegion.Rows.Count
End Function
' Copy table from a sheet into another after applying filter and returns number of rows
' aka AdvancedFilter
' +++ For now, all should be within same workbook +++
' RSource = Range of source, full range
' ShSource= Sheet name of source table
' RFilter = Range having filter, 1st row should have similar values from RSource
' ShFilter= sheet having RFilter
' RCopyTo = Range to copy filtered output to, expect to have entire sheet cleared and filled again
' ShCopyTo= Sheet to copy to
'
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
Workbooks(WB).Worksheets(ShSource).Range(RSource).AdvancedFilter xlFilterCopy, Workbooks(WB).Worksheets(ShFilter).Range(RFilter), Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo), False
ANmAdvancedFilter = Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo).CurrentRegion.Rows.Count
End Function
ShSource, RSource, ShFilter, RFilter, ShCopyTo, Optional RCopyTo = "A1", Optional WB = "This"
Views 97
Downloads 51
CodeID
DB ID
ANmarAmdeen
610
Revisions
v1.0
Thursday
September
15
2022