Apply filter from a master worksheet to multiple worksheets

Question

I am trying to apply filter from a master worksheet to multiple worksheets in the same workbook.

eg.

There are 3 worksheets in the workbook, with following fields on each sheet:

1. Country

CountryCode, CountryName

1. Sales

CountryCode, SalesAmount

1. Inventory

CountryCode, InvAmount

When i filter Country sheet by FRA, JPN, and USA, i expect Sales and Inventory sheets will follow the same filter criteria automatically, and display only FRA, JPN, and USA rows.

It doesn't seems to be too complicated, but i have been trying to code in VBA using AdvancedFilter method, for a few days with no luck!

Many thanks for your help!

Answer 1

You want something like this I think, insert the range you want to filter in each worksheet where I've put xx. Replace i, ii and iii with the country codes for FRA, JPN and USA.

Please note this is filtering on the country code as opposed to country name as it is the common field.

Option Explicit
Sub Apply_Filter()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        .Range("xx").AutoFilter Field:=1, Criteria1:=Array("i", _
        "ii", "iii"), Operator:=xlFilterValues
    End With
Next

End Sub