CODEWITHSUNDEEP
excelvba
Stella1234
Stella1234

Reputation: 35

Excel Table - Copy autofilter across multiple sheets

I have 5 different sheets with different sets of data, however I would like to filter them all by the same date. I am looking for a way to automate this either with VBA or by Advanced Filter (?) and I am struggling to find a way how.

What I am after is to apply one filter to one 'master' worksheet, and for the remaining 4 sheets to automatically apply the same date filter as the one in the master worksheet. For example, if I would like to show all the data for November of 2015, if I filter this in Sheet 1, hopefully Sheets 2, 3 and 4 will show the data for November 2015 too.

I have found many VBA solutions online that specify the filter criteria inside the coding, but the criteria will change depending on the requirements of the user. Is there a way to copy across the filter from the master sheet? This is the code I have found for the criteria coded inside;

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=3, Criteria1:="=True"
End Sub

Thanks in advance!

Upvotes: 0

Views: 817

Answers (1)

Anand
Anand

Reputation: 373

Assuming you are only selecting one criteria in master sheet, you can use VBA ComboBOX control in master worksheet to populate filter criteria. As a next step you can write code as shown below for ComboBox change event that will apply filters to all remaining four worksheets.
In this ComboBox Change event macro you can take the value of a ComboBox and pass it as a filter criteria for all four sheets as below. Note that in my case i have considered table with only two columns and first column has a filter criteria of date in each worksheet and second column in table has some dummy values. 

Private Sub ComboBox21_Change()
Date = ComboBox21.Value
Worksheets("Sheet1").Range("Table1").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, Date)
Worksheets("Sheet2").Range("Table2").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, Date)
Worksheets("Sheet3").Range("Table3").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, Date)
Worksheets("Sheet4").Range("Table4").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, Date)
End Sub

Upvotes: 1

Related Questions