MrExcel.com - Your One Stop for Excel Tips & Solutions

Pivot table doesn't refresh when base data changes

Posted by Sarah on October 24, 2001 9:08 PM

When I refresh data using the 'New Database Query' function in Excel, my pivot table which accesses this data doesn't include the new data added when I tell it to refresh.

This only happens with a macro, if I do the data refresh and pivot refresh manually the pivot will automatically pick up the new data added.

Is there anyway to write the macro so that it automatically picks up new data

thanks
Sarah

Posted by Mark W. on October 25, 2001 6:04 AM

Are you using a cell reference or a defined name
at Step 2 of 4 of the PivotTable Wizard?

Posted by Mark W. on October 25, 2001 6:09 AM

Oops... perhaps I made a rash assumption...

Is your PivotTable's data coming from an External
Data Source? If so, please describe that source.

Posted by Tom Urtis on October 25, 2001 7:23 AM

Would this help?

Sarah,

Right click on your sheet tab containing the pivot table, then left click on "View Code", and try pasting this code into sheet the module. I wasn't clear if your data et. al. is on the same sheet as your pivot table, so to be on the safe side this code will update your pivot table automatically when its worksheet is activated. Hopefully this will get you at least a little closer to what you want to accomplish. Thanks to Chip Pearson on his web site for most of this code.

Hope this helps.

Tom Urtis

Sub Worksheet_Activate()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

Posted by Sarah on October 28, 2001 3:15 PM

Re: Oops... perhaps I made a rash assumption...

Hi Mark
sorry about the delayed response - yes the data is coming from an external source - a Database Query set up to go into our financial system. When the data comes in Excel names it as 'external data' but I can't use this in the pivot as the range name (it won't let me) so I am using a user - defined name. I have had to use a range name as the data is on a separate sheet to the pivot

kind regards
Sarah