VBA to update pivots in a hidden worksheet

[Chrissie1970]

Hi everyone.

I have a test workbook with four worksheets:
tab 1 - 2.1.1 Critical Role Analysis = source data sheet;
tab 2 - Dropdown = test area containing a drop down fed from PivotTable3 results;
tab 3 - HideMe8 = contains a pivot table called PivotTable3 to feed a drop down in tab 2 (tab 3 must remain hidden);
tab 4 - Pivot2 = contains a second pivot table called PivotTable1 as a checker that the below code is auto-updating multiple pivots (tab 4 needs to remain hidden).

Incidentally, this test book is an extract from a much larger workbook containing many pivots in many hidden tabs - I just didn't want to mess up the master workbook again!

So, when tab 3 & 4 are not hidden, the pivots will auto-refresh but when they are hidden, neither will auto-refresh until I unhide the tabs; the drop down on tab 2 is therefore, not auto-updated until I unhide tab 3.

Basically, I need all my pivot tables in hidden tabs to automatically refresh when there is a data change in the source data sheet. Again, the pivot tabs must remain hidden as the end-users are not Excel savvy enough to follow instructions to manually refresh.

The following code is placed in the source data (tab 1) and auto refreshes both pivots but only when the tabs are unhidden:

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

I am very new to VBA so could really use your expertise to expanding the code so it refreshes all pivots in all hidden tabs plus I need confirmation of where to place the code - does it go in the named sheet (tab) or ThisWorkbook under Microsoft Excel Object or as a Module. I promise I have spent many days scouring the internet for a solution but its all too techie and confusing for my level on knowledge!

Thank you so much for your time til now and I really look forward to hearing from someone....I hope the above makes sense.

 

[Trevor G]

The following code should guide you.

Sub updateall()
 Dim pvt As PivotTable
    For Each pvt In Worksheets("Goal").PivotTables 'Change the sheet name to the hidden sheet name
        pvt.RefreshTable
    Next pvt
End Sub

 

[Chrissie1970]

Great, thank you for your time to reply Trever G....I will give it a go and let you know the outcome....I need training!!