Copy AutoFilter Values To Other Worksheets
The code below demonstrates how a user can select an autofilter in Sheet1 and have the AutoFilter values copied to a columns in other worksheets. For example, suppose the user selects an AutoFilter in Column A of Sheet1. When the filtered results are displayed for Sheet1, the same filters will be applied to Column A of Sheet2 and Sheet3. This method assumes the data values in all three sheets of Column A contain the same key values.
Program Code
Option Explicit
' *********************************************
' Define Workbook and Worksheet Variables
' *********************************************
Dim wkbCountryData As Workbook
Dim wksCountry As Worksheet
Dim wksSales As Worksheet
Dim wksInventory As Worksheet
' *********************************************
' Define Other Variables
' *********************************************
Dim lngLastSalesRow As Long
Dim lngLastInventoryRow As Long
Dim w As Worksheet
Dim f As Long
Dim filterArray()
Dim currentFiltRange As String
Dim strSalesRange As String
Dim strInventoryRange As String
Public Sub UpdateAutoFilters()
' *********************************************
' Set Workbook/Worksheet Variables
' *********************************************
Set wkbCountryData = ActiveWorkbook
Set wksCountry = wkbCountryData.Sheets("Country")
Set wksSales = wkbCountryData.Sheets("Sales")
Set wksInventory = wkbCountryData.Sheets("Inventory")
' *********************************************
' Count Unfiltered Row In Sales and Inventory
' *********************************************
Application.EnableEvents = False
wksSales.AutoFilterMode = False
wksInventory.AutoFilterMode = False
Application.EnableEvents = True
lngLastSalesRow = wksSales.Cells(Rows.Count, "A").End(xlUp).Row
lngLastInventoryRow = wksInventory.Cells(Rows.Count, "A").End(xlUp).Row
strSalesRange = Range(wksSales.Cells(1, 1), wksSales.Cells(lngLastSalesRow, 2)).Address
strInventoryRange = Range(wksInventory.Cells(1, 1), wksInventory.Cells(lngLastInventoryRow, 2)).Address
' *********************************************
' If Main Worksheet Filters Are Off,
' Turn Off Filters On Other Worksheets
' *********************************************
If wksCountry.AutoFilterMode = False Then
wksSales.AutoFilterMode = False
wksInventory.AutoFilterMode = False
Exit Sub
End If
' *********************************************
' Record Filter Settings For The Country WS
' *********************************************
With wksCountry.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next f
End With
End With
Call LoadFiltersToOtherWorksheets(wksSales, strSalesRange)
Call LoadFiltersToOtherWorksheets(wksInventory, strInventoryRange)
End Sub
Private Sub LoadFiltersToOtherWorksheets(w As Worksheet, FilterRange As String)
' *********************************************
' Load Filter Settings From Country To Sales
' And Inventory
' *********************************************
Application.EnableEvents = False
For f = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(f, 1)) Then
If filterArray(f, 2) Then
w.Range(FilterRange).AutoFilter field:=f, _
Criteria1:=filterArray(f, 1), _
Operator:=filterArray(f, 2), _
Criteria2:=filterArray(f, 3)
Else
w.Range(FilterRange).AutoFilter field:=f, _
Criteria1:=filterArray(f, 1)
End If
End If
Next f
Application.EnableEvents = True
End Sub
