March 12, 2014

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:

9 thoughts on “Apply Autofilter Across Multiple Excel Worksheets

  1. hello, thank you for this good work, i have a question:
    can i apply this on google spreadsheets?

  2. 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.

  3. Hello sir,
    plz give VBA code for…
    sheet 1 in Jan FEB, march, May, Jun… etc
    sheet 2 is All data ( sheet 2 in column A1 – jan, A2- Feb… etc )
    macro run & crate Tab as given name in sheet 1 jan, feb etc
    and past data jan tab – Jan all data
    plz look and give me code & email me

  4. Dear Sir,
    I have a question regarding the same autofilter VBA code – I want to apply the autofilter only for those sheets which are visible at the time of code running in workbook. Also, from the current active sheet to last visible sheet.
    Also, The code which you provide here having a issue that when we run this code it filters all the sheets except the required one which don’t have the same criteria as per the code.

Comments are closed.