Refreshing a variety of Pivots tables

[Lorna B]

Hi

Can someone help me?

I have a document that contains a variety of pivot tables run from different spreadsheets. Is there any way you can have a programmed button to press which refreshes all the pivot tables in a wookbook?

Please note that there are a few on different sheets of the wookbook.

It saves me having to refresh all individual ones?

Any help at all would be much appreciated!

Thanx

Lorna

[DonkeyOte]

Perhaps

ActiveWorkbook.RefreshAll

(pending where code is located you may want to swap Active with This)

[romperstomper]

If you just want to refresh pivot tables, then you could also use:

Sub RefreshAllPivots()
   Dim pc As PivotCache
   For Each pc In ActiveWorkbook.PivotCaches
      pc.Refresh
   Next pc
End Sub

[Lorna B]

How do I put this into a formula for a cell?

[romperstomper]

You can't. You did ask for a macro to run from a button, didn't you?

[Lorna B]

So do i need to add a macro with the above and when i open the spreadsheet this will automatically run??

Sorry - I've mastered quite a few things on excel but not this.

Thanx

[romperstomper]

That is a macro.
If you just want them to refresh when the workbook is opened, then that is an option in the pivot tables themselves - you don't need any code at all.

[Lorna B]

Sorry - New to all this,

Im purely self taught but these little things are difficult.

where would i get something like that - when the workbook is opened the tables refresh?

[Lorna B]

opps i found it

thanx for your help!!

Lorna

[Lorna B]

I have another question.....

When i open the pivot table options it give me the option to refresh when open and also refresh every ?? but the latter is greyed out? How can I choose this option?

[romperstomper]

That option is only available if your data comes from an external source - not within the workbook.