Apply Autofilter Across Multiple Excel Worksheets

Apply Autofilter Across Multiple Excel Worksheets Using VBA (Macro)
A website visitor asked this question:
From: Bhadrik
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:
Sub apply_autofilter_across_worksheets()
‘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 Worksheets(q)
‘ 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*”
End With
Next q
End Sub

Watch the training video below to learn how to apply an AutoFilter across multiple worksheets in Microsoft Excel:


5 thoughts on “Apply Autofilter Across Multiple Excel Worksheets

  1. Pingback: Copy auto filtered data to another worksheet automatically with VBA | Excel VBA Training Videos

  2. Chun

    Hello, thank you for this, but what if my Autofield:= is all different worksheets? meaning, for example, i want to filter Version number, but this column is not always at the same column, it could be E, could be F, could be AA. How do you encounter that?

    Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *