[SOLVED] VBA - Worksheet_FollowHyperlink - clear filters then set new filter

VBA - Worksheet_FollowHyperlink - clear filters then set new filter

I need to create multiple hyperlinks in a summary page which link to a filtered view of my master sheet.

I have created a hyperlink in each cell to itself and then have used the following Worksheet_FollowHyperlink method to create the filters. i.e. If I click the link in summary page L2 it returns a filtered view of my Master table where field 11 = "Yes". L3 returns a filtered view for field 12 = "Yes" and so on.

The problem I have is that I have to manually clear all filters in my master sheet before clicking on a new link. If I don't manually clear filters then the filters are compounded resulting in a much reduced dataset.

I have tried the following ActiveSheet.ShowAllData but this isn't working.

My full macro is below

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If

If Target.Range.Address = "$L$2" Then

    Sheets("Master").Select
    ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=11, Criteria1:="Yes"
    End If
    
If Target.Range.Address = "$L$3" Then
    
    Sheets("Master").Select
    ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=12, Criteria1:="Yes"
    End If

End Sub

Solution

You are resetting the filter on the active sheet - which ist not your Master-Sheet!

Change that first part to code like this:

With Sheets("Master").ListObjects("Table")
    With .AutoFilter
        If .FilterMode Then
            .ShowAllData
        End If
    End With
End With