Run a Macro without being on the sheet

Question

I am trying to activate a macro while on a different worksheet. I have coded a Macro so that when I select an option from a drop down list it updates a pivot filter field on another sheet. The problem is I have to be on the active sheet for it to work. I have the following code if someone could please help, thanks

Sub FilterPageValue()    
   Dim pvFld As PivotField    
   Dim strFilter As String    
   Set pvFld = ActiveSheet.PivotTables("PivotTable3").PivotFields("Reporting entity")    
   strFilter = ActiveWorkbook.Sheets("Sheet1").Range("b2").Value       
   pvFld.CurrentPage = strFilter        
End Sub    

Answer 1

Filter Pivot Table Via DropDown

• You have to adjust the name of the worksheet that contains the pivot table (I picked Sheet2) in both codes.

Standard Module e.g. Module1

• This code will run manually.

Option Explicit

Sub FilterPageValue()
   
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Worksheet with cell 'B2'.
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    
    ' Worksheet with 'PivotTable3'.
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2") ' adjust! this
    
    
    Dim pvFld As PivotField
    Set pvFld = dws.PivotTables("PivotTable3").PivotFields("Reporting entity")
    
    Dim strFilter As String
    strFilter = sws.Range("B2").Value
    
    pvFld.CurrentPage = strFilter

End Sub

Sheet Module Sheet1

• This code will run automatically each time you change the value in cell B2 of Sheet1 (dropdown).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Range("B2"), Target) Is Nothing Then
       
        ' Worksheet with 'PivotTable3': adjust!
        Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Sheet2")
        
        Dim pvFld As PivotField
        Set pvFld = dws.PivotTables("PivotTable3").PivotFields("Reporting entity")
        
        Dim strFilter As String
        strFilter = Target.Value
        
        pvFld.CurrentPage = strFilter
    
    End If

End Sub

Follow-Up

• You can rewrite the code as a sub with arguments to easily run the code for multiple pivot tables.

Sheet Module Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Dim pFilter As String: pFilter = Target.Value
        UpdateCurrentPage "Sheet2", "PivotTable3", "Reporting entity", pFilter
        UpdateCurrentPage "Sheet3", "PivotTable3", "Reporting entity", pFilter
    End If
End Sub

Sub UpdateCurrentPage( _
        ByVal WorksheetName As String, _
        ByVal PivotTableName As String, _
        ByVal PivotFieldName As String, _
        ByVal PivotPage As String)
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(WorksheetName)
    Dim pTable As PivotTable: Set pTable = ws.PivotTables(PivotTableName)
    Dim pField As PivotField: Set pField = pTable.PivotFields(PivotFieldName)
    pField.CurrentPage = PivotPage
End Sub