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

 

[Scottyfras111]

Hi Dave - I have tried to replicate this and am failing - please can you advise when you have a minute?

VBA - filter multiple sheets based on cell ref

https://www.mrexcel.com/board/threads/vba-to-filter-multiple-sheets-based-on-a-cell-on-another-sheet.1095044/ I am trying to use this to solve my problem but keep getting error - vba subscript out of range error 9 Please help Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As...

www.mrexcel.com

 

[Joe4]

Hi Dave - I have tried to replicate this and am failing - please can you advise when you have a minute?

Duplicate to: VBA - filter multiple sheets based on cell ref

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.