Apply Autofilter Across Multiple Excel Worksheets

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010



A website visitor asked this question:
From: bhadrik
Message: I want to apply filter to multiple sheets with same datas 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 Autofilter 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 maximu 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 datatype 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 Autofilter 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:





Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010