Updating all pivots when opening workbook

Hi,
I have a spreadsheet with a number of sheets in it for our sales team to record orders received and pending; the first sheet contains pivots which relate to the other sheets (a sheet for each financial quarter) (i.e. one pivot for sheet 2, one pivot for sheet 3 etc)

Is there a macro which will allow me to update all of the pivots when the workbook is opened? Or, even better, one where they will be updated when the first sheet is accessed?

The only one I have found when searching is:

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
End Sub

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

but that one doesn't work (and I'm not good enough with Macros to know what I should be changing to fit my worksheet)

And then...
This workbook is being replicated four times (for the four different business sectors in our company), and I have a summary spreadsheet for our Sales director which will combine the pivots in the four spreadsheets. Is there a macro which will do the same thing as above? (i.e. update the pivots which have been taken from a different workbook?)

Many thanks in advance for your help!

Hi

At which code line does the macro halt with the yellow highlight?

Rgds

hi Roy - thanks for this - unfortunately, it doesn't seem to work (I'm updating the sheets to check) either when I update then go to the pivot sheet, nor when I shut down and reopen Excel.
Should I be putting this macro on the sheet where I have the pivots or on the sheets where the pivot data is coming from?

Richard - I don't understand what you mean? I don't have any yellow highlight nor the macro halting?

Hi,
Your original post said the macro wasn't working. I implied that you meant you'd run it and it fell over. From this reply it seems that the macro actually processes without a problem, but it does not seem to be refreshing the pivots as you expect.
Is that all pivots, or is it updating some but not others?

Rgds

Yes, that's it - I update something in the lines which the pivot is looking at, but when I then go back to the pivot, it doesn't update till I manually hit refresh. (i.e. it doesn't appear, from what I am experiencing, that the macro is running at all, even though I have macros enabled).

None of the pivots are updating.

Sorry for being ambiguous (I'm really not good with macros! )

Hi,

Perhaps stating the obvious, but are you actually running the macro?

Or are you expecting it to kick in automatically just because you alter something in the data?

Rgds

Hi Roy - I have done this - I have copied and pasted the second code into the VB editor for the sheet which the pivots are on. This is the only data in there. When it is just in the sheet, then nothing happens, either when I just update the data, then move back to the sheet with the pivots on, nor when I close and reopen the spreadsheet.

I have also put it into the ThisWorkbook one. However, when I put it into the ThisWorkbook, then closed and reopened the spreadsheet, I got the following error:

Compile Error:
Sub or Function not defined

It stops at the row:
Private Sub Workbook_Open()

What am I doing wrong?

Thank you so much with your help so far - my brain and macros just don't seem to gel together!

Does anyone have an idea on why this keeps breaking?

Hiya,

Here it is (zipped file) - I have taken out all company personal data, so please ignore the blank cells. There is also some formatting and another time-based macro on the quarters tabs, but they shouldn't affect things, should they?

Thanks in advance for your help and your patience!