Updating multiple pivots in Excel

Question

Hi everyone

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.

BO 

Answer 1

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

Answer 2

Thanks so much Hans.

That's a real help!

Answer 3

Hans

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...

BO

Answer 4

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

Answer 5

Hans

This is brilliant - works perfectly!

Thanks so much again.

BO