Updating all pivots when opening workbook

**JoFo** · 12-19-2008, 06:45 AM

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:

Please Login or Register  to view this content.

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!

**royUK** · 12-19-2008, 07:17 AM

Try this

Please Login or Register  to view this content.

**Richard Buttrey** · 12-19-2008, 07:17 AM

Hi

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

Rgds

**JoFo** · 12-19-2008, 07:29 AM

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?

**Richard Buttrey** · 12-19-2008, 07:55 AM

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

**JoFo** · 12-19-2008, 07:57 AM

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!

)

**Richard Buttrey** · 12-19-2008, 08:49 AM

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

**JoFo** · 12-19-2008, 08:55 AM

I was expecting it to kick in automatically when I opened up the workbook or the worksheet that I had the pivots in - essentially, I'm working with a team of technophobes. They will not remember to update the pivots themselves, so I want something that will do it for them! (if it was a case of manually running the macro, then they could click on the update pivot exclamation mark just as easily)

**royUK** · 12-19-2008, 09:05 AM

Your auto_open macro should run on opening, but is worded wrong. it should be

Please Login or Register  to view this content.

This has however been superseded by the workbook events, in this case Open.

Please Login or Register  to view this content.

You could also use

SheetActivate - run the macro whenever a sheet is activated

Copy Code to a Worksheet Module

Event code will run automatically when something specific occurs in the workbook.

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code that you want to use
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

**JoFo** · 12-19-2008, 09:20 AM

Ok....

Please could I clarify? Do I just copy and paste in your second macro code into the Visual Basic Editor to replace everything that is currently in there? Or is there anything else? And should it be in the sheet that the pivots are on, or on the sheets where the data is?

I've copied and pasted everything in the second code into the editor for the sheet where the pivots are, but it still isn't updating when I change the data and then move back into the sheet where the pivots are.

**royUK** · 12-19-2008, 09:33 AM

Remove your auto open macro.

Follow the instructions in my last post and copy & paste the code into the workbook module as described.

When the workbook opens and/or a sheet is activated yor macro will un

**JoFo** · 12-19-2008, 09:49 AM

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!

**JoFo** · 12-23-2008, 10:13 AM

Does anyone have an idea on why this keeps breaking?

**royUK** · 12-23-2008, 11:17 AM

Attach the workbook

**JoFo** · 12-23-2008, 11:26 AM

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!