VBA to update pivots in a hidden worksheet

Chrissie1970

New Member
Joined
Aug 12, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
The following code should guide you.

VBA Code:
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

New Member
Joined
Aug 12, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top