Refresh pivot tables

[GLENEE]

I want the pivot tables in an Excel 97 spreadsheet to refresh automatically when a user makes a change. The code used on save(from a recorded macro) is :

[Worksheets("Sheet1").select
Worksheets("Sheet1").PivotTables(1).PivotTable "", xlDataAndLabel
Application.CommandBars("PivotTable").Visible = True
Worksheets("Sheet1").PivotTables(1).RefreshTable
Application.CommandBars("PivotTable").Visible = False]

I get runtime error 1004 "RefreshTable method of PivotTable class failed"
The refresh works fine when run as a macro.
Thanks

 

[xlbo]

Use the worksheet_CHANGE event


code would be similar to

for each pt in thisworkbook.pivottables
pt.refreshtable
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question