Update all Dynamic Tables when selecting a Worksheet

Question

I need all the dynamic tables in my spreadsheet to be updated every time I select one of my worksheets that serves as Dashboard. I used the most logical code, but I think something is missing, since the macro does not run, since it returns "compilation error" to PivotTables:

Private Sub Worksheet_Activate()

    ThisWorkbook.Sheets.PivotTables.RefreshTable

End Sub

Answer 1

Unfortunately it is not possible to assign any action to all Tables pivots on a single line, as if they were a single object. You would have to make a loop to update them one by one, so:

Private Sub Worksheet_Activate()

'Dimensionar variáveis
    Dim pt As PivotTable
    Dim ws As Worksheet

'Atualiza Pivot Tables uma a uma
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
            Next pt
        Next ws

End Sub