Pivot table filter reference from cell value

[JStirfry]

Hello. I asked a question yesterday in a thread that was from August of 2013:

https://www.mrexcel.com/forum/excel-questions/625300-pivot-table-filter-reference-cell-2.html


I'd like to ask the same question here in a new post. I used versions of the following code.

Code in the Sheet Code Module of the Sheet that has my DataValidation Cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
   
    sField = "GID"  'Field Name
    sDV_Address = "$C$2" 'Cell with DV dropdown to select filter item.

    If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
        Target.Cells.Count > 1 Then Exit Sub
    
    On Error GoTo CleanUp
    Application.EnableEvents = False

    Call Single_Page_Filter(Sheets("Expense") _
        .PivotTables(1).PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Actuals") _
        .PivotTables(1).PivotFields(sField), Target.Text)
       
CleanUp:
    Application.EnableEvents = True
End Sub

Code in the Standard Code Module

Public Function Single_Page_Filter(pvtField As PivotField, _
        ByVal sValue As String)
    
    On Error GoTo ErrorHandler
    With pvtField
        .ClearAllFilters
        .CurrentPage = sValue
    End With
    Exit Function

ErrorHandler:
    Select Case Err.Description
        Case "Application-defined or object-defined error"
            MsgBox "PivotItem: " & sValue & " not found in PivotTable: " _
                & pvtField.Parent.Name
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
End Function

The method above works great, however, what I'm looking to do is apply the sheet code module above to a "template" worksheet that will be copied and renamed over and over again. I don't want to have to open up the sheet code module and edit the sheet name in the following line of code every time if I don't have to.

Call Single_Page_Filter(Sheets("Expense") _
.PivotTables(1).PivotFields(sField), Target.Text)

Is there a way to modify this to just point to the current worksheet rather than a specific worksheet name?

Thanks in advance!

 

[JStirfry]

I was able to simplify the above code, but still can't figure out how to filter the active sheet pivot table based on the name of the active sheet. This code will filter it if I click off on another worksheet and then click back, but not if I just rename the worksheet, which is want I want.
Please help!

Sheet Code:

Private Sub Worksheet_Activate()
    
    Run "AllWorksheetPivots"
    
    Dim pf As PivotField


    Set pf = ActiveSheet.PivotTables().PivotFields("Room")


    'Clear Out Any Previous Filtering
      pf.ClearAllFilters


    'Filter on room name (worksheet name) items
      pf.CurrentPage = ActiveSheet.Name
    
End Sub

Module Code:

Sub AllWorksheetPivots()


Dim pt As PivotTable


    For Each pt In ActiveSheet.PivotTables


        pt.RefreshTable


    Next pt


End Sub

Thanks,
Justin

 

[JStirfry]

I'm still looking for assistance on this...maybe it's something that can't be done?!?!