Apply Autofilter Across Multiple Excel Worksheets Using VBA (Macro)
A website visitor asked this question:
Message: I want to apply filter to multiple sheets with same data e.g if I apply filter to a column in sheet one the filter should reflect in all the sheets at the same time.
We have learnt how to use AutoFilter in Excel worksheet to extract information from our worksheet data quickly. Applying an Auto-filter across multiple worksheets without a macro wasn’t possible. We had to write a macro which first counts the total number of sheets and then applies the Autofilter on each of the sheets. Our filter criteria was ‘H*’ under the header ‘name’. This just means, we wish to filter all names that begin with ‘H’ and we don’t care what comes after that. The ‘*’ in the search criteria is also referred to as a wild card character and represents multiple characters. If you wished to search for a name starting with ‘H’ but with a maximum of 5 characters you would use ‘H?????’. In this case the ‘?’ represents a single character and you would be easily able to find ‘Harry’.
The solution was found using the following macro:
‘After giving the macro a name we define two variables p and q which will be of data type integer
Dim p As Integer, q As Integer
‘Using the count function we assign p the value of the number of sheets in our workbook
p = Worksheets.Count
‘We loop through all the worksheets from sheet1 to the last counted sheet p
For q = 1 To p
‘ With each worksheet selected in the looping process we apply the Auto-filter with a specific criteria. We wish to filter out all persons whose name begins with H
.Range(“A1″).AutoFilter field:=1, Criteria1:=”H*”
Watch the training video below to learn how to apply an AutoFilter across multiple worksheets in Microsoft Excel: