PivotTable RefreshTable 2

[Stretchwickster]

I've added a command button directly to my excel sheet. Then I recorded what happens when I refresh my table and it generated the following VBA code:

ActiveSheet.PivotTables("PivotTable3").PivotSelect "'4'", xlDataAndLabel
  ActiveSheet.PivotTables("PivotTable3").RefreshTable
  ActiveSheet.PivotTables("PivotTable4").PivotSelect "'5'", xlDataAndLabel
  ActiveSheet.PivotTables("PivotTable4").RefreshTable

I pasted this code into my command button click event but when I click it at run-time, it gives the following error: "RefreshTable method of PivotTable class failed". Anyone know what the problem is or how to get it to work properly.

Any help would be much appreciated!

Clive

 

[xlbo]

If the button is a "Controls Toolar" button, you will need to set the "Take Focus On Click" property to false otherwise there is no "Activesheet" as the button has the focus

Plus, I would use
For each pt in activesheet.pivottables
pt.refresh
next

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Stretchwickster]

Thanks Geoff! Once again you solve my problem in record time!! Clive

 

[Quintios]

That's twice today yu've helped me out Geoff...

I'm just going to have to do a search on you and read all your posts so I won't have to beat my head against the wall anymore.

Onwards,

Q-