Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto refresh pivot table
Dave,
This code worked wonderfully until I edited a column name in the data source. Now the pivot table no longer refreshes automatically. Any suggestions as to how I can get it working again? Thanks, Lori "Dave Peterson" wrote: Even if you could, I'm not sure that's a good idea. I figure that MS made refreshing the pivottables a manual effort to keep the time between worksheet changes at a minimum. Imagine lots of pivottables and each one trying to recalculate each time you change a value in the pivottable range. But maybe you could refresh it when you select the worksheet that contains the pivottable (is it on a different worksheet???) rightclick on the worksheet tab (with the pivottable) and click on select code. Then paste this in: Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT End Sub If you have lots of pivottables in different sheets, you can get them all with a line like: ThisWorkbook.RefreshAll Tom Nichol wrote: I am using Excel 2000. I have a pivot table that summarizes data from a database on a separate sheet. When I delete rows from the database sheet, I must click the refresh button on the Pivot Table before it will recognize those deletions. Is there a way to have the pivot table automatically refresh the instant the rows are deleted? -- Dave Peterson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto refresh pivot table
It sounds like you may need to recreate the pivottable.
Then test to see if it works. Lori wrote: Dave, This code worked wonderfully until I edited a column name in the data source. Now the pivot table no longer refreshes automatically. Any suggestions as to how I can get it working again? Thanks, Lori "Dave Peterson" wrote: Even if you could, I'm not sure that's a good idea. I figure that MS made refreshing the pivottables a manual effort to keep the time between worksheet changes at a minimum. Imagine lots of pivottables and each one trying to recalculate each time you change a value in the pivottable range. But maybe you could refresh it when you select the worksheet that contains the pivottable (is it on a different worksheet???) rightclick on the worksheet tab (with the pivottable) and click on select code. Then paste this in: Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT End Sub If you have lots of pivottables in different sheets, you can get them all with a line like: ThisWorkbook.RefreshAll Tom Nichol wrote: I am using Excel 2000. I have a pivot table that summarizes data from a database on a separate sheet. When I delete rows from the database sheet, I must click the refresh button on the Pivot Table before it will recognize those deletions. Is there a way to have the pivot table automatically refresh the instant the rows are deleted? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto refresh pivot table | Excel Discussion (Misc queries) | |||
Auto refresh pivot table | Excel Discussion (Misc queries) | |||
Auto refresh pivot table on Save | Excel Discussion (Misc queries) | |||
auto refresh of pivot table | Excel Discussion (Misc queries) | |||
How do I get a web query to auto-refresh before a pivot table aut. | Excel Discussion (Misc queries) |