VBA to filter multiple sheets based on a cell on another sheet

[lydytunes]

I've been doing some digging and have found a few answers but nothing that does specifically what I'm trying to do. I have an excel file with over 10 sheets and four of these sheets I need to apply a filter to based on a selection made on another sheet. I'm currently using the code below, but I have to select each sheet in order for the filtering to work.

Private Sub Worksheet_Activate()
Unprotect Password:="notouch"
On Error Resume Next
Selection.AutoFilter Field:=2, Criteria1:=Sheets("Choose Your Department").Range("D3").Value
Protect Password:="notouch"
End Sub

I'm looking for a way to get the filtering to work so the users don't have to select each tab to make it happen. The filtered results are then being used on other tabs the users will be editing. Any help would be appreciated.

Thanks.

 

[dmt32]

Hi,
Try following

Place in your worksheets (Choose Your Department) code page

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    If Target.Address = "$D$3" Then
        For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
            With ws
            .Unprotect Password:="notouch"
                If .AutoFilterMode Then .ShowAllData
                .Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Target.Value
            .Protect Password:="notouch"
           End With
        Next ws
    End If
End Sub

When value if cell D3 is changed, filter should be applied to specified sheets.

Change the sheet names shown in RED code applies to as required.

Hope helpful

Dave

 

[lydytunes]

Dave,

Thanks for this code. I knew it was some type of Array wording but couldn't figure it out. This worked perfectly.

 

[dmt32]

Dave,

Thanks for this code. I knew it was some type of Array wording but couldn't figure it out. This worked perfectly.

Most welcome - thanks for feedback

Dave