Prevent msg box when updating Pivot Table by macro? (DisplayAlerts = False doesn't work)

Posted by Catherine Munro on February 07, 2002 1:18 PM

I have summary data in a pivot table, based on frequently changing source data. I use a macro to refresh the pivot table when source changes (PivotTables("PivotTable1").RefreshTable). It works beautifully except for one thing -- when the new pivot table contains more rows than the old, I get a message box reading "Do you want to replace the contents of the destination cells?"

The answer should always be Yes, but no matter where I place "DisplayAlerts = False" within the code, I get this box when I reach the RefreshTable line. Is there another way to tell it to overwrite, or to send a "Yes" and prevent that msg box from displaying?

A little bit batty,
Catherien

Posted by faster on February 07, 2002 2:53 PM

I think you may get this message because of data
outside the pivot table. But the below code trapped
the msgbox for me?

Application.DisplayAlerts = False
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Application.DisplayAlerts = True

Posted by Catherine Munro on February 07, 2002 3:00 PM

Tried that -- but I'll see if something outside the box is causing it -- maybe I can just do a ClearContents on the non-pivot area. (Didn't want to clear the Pivot Table itself -- much easier & faster to just refresh it by macro than to rebuild it!)

Thanks for the suggestion, faster!
Cath