Wednesday, April 04, 2012 12:54 PM
I have an Excel file which has a number of pivots in it across a number of different tabs (NORTH, SOUTH, EAST & WEST). They are all linked to the same data source (the RAW_DATA tab) and I would like tham all to be refreshed when new data rows are added to the RAW_DATA tab. At present I'm having to refresh each pivot individually which is a bit of a pain. Ideally I'd like the refresh to happen I click into the NORTH, SOUTH, EAST & WEST tabs but other ways may work better.
Thanks in advance.
Wednesday, April 04, 2012 1:33 PM
Press Alt+F11 to activate the Visual Basic Editor.
Double-click the ThisWorkbook node of your workbook in the Project Explorer on the left hand side.
Copy the following event procedure into the module window:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Me.RefreshAll End Sub
Make sure that you save the workbook as a .xlsm, .xlsb or .xls workbook, not as .xlsx, for the .xlsx type doesn't save VBA code.
Regards, Hans Vogelaar
- Marked As Answer by ByronOne Wednesday, April 04, 2012 2:32 PM
Wednesday, April 04, 2012 2:32 PM
Thanks so much Hans.
That's a real help!
Wednesday, April 04, 2012 2:39 PM
Is it possible for the refresh to happen only the first time you click on each of the NORTH, SOUTH etc tabs. I've noticed that the data is refreshed everytime you click into the tabs - which is what i requested - but actually I'd probably only want it to happen the first time after I had added data to the RAW_DATA tab. I think end users may dislike the fact that it refreshes each time...
Apologies for not being clearer...
Wednesday, April 04, 2012 3:37 PM
One option would be to refresh the pivot caches whenever the user modifies a value in the source table on the RAW_DATA sheet, but that might generate too much overhead.
Here is another option:
1) Change the code in ThisWorkbook to
Public blnRefresh As Boolean Private Sub Workbook_SheetActivate(ByVal Sh As Object) Select Case Sh.Name ' Only refresh if we activate a sheet with pivottable(s) Case "NORTH", "EAST", "SOUTH", "WEST" ' Only refresh if data have changed If blnRefresh Then Me.RefreshAll ' Flag that we have refreshed blnRefresh = False End If End Select End Sub
2) Right-click the sheet tab of the RAW_DATA sheet.
Select View Code from the context menu.
Add the following code:
Private Sub Worksheet_Change(ByVal Target As Range) ' Modify the range as needed If Not Intersect(Range("A:F"), Target) Is Nothing Then ' Flag that source data have been changed ThisWorkbook.blnRefresh = True End If End Sub
Regards, Hans Vogelaar
Thursday, April 05, 2012 10:42 AM
This is brilliant - works perfectly!
Thanks so much again.
- Marked As Answer by ByronOne Thursday, April 05, 2012 10:42 AM