Visual Basic Pivot Table Refresh, How to stop dialog boxes popping up?

[camwild79]

Hi, sometimes when I refresh a pivot table a dialog box pops up and asks 'Do you want to replace the contents of the destination cells..' This is really annoying since I refresh the pivot table within some code in a macro that runs automatically. Therefore when this diolog box comes up and asks this question, nothing else can happen until I answer it! Is there any way to make it either not ask me questions at all, or simply default to answering yes to it??? Please help! thanks very much

ps. I have tried it with both these different code options

Set pvtTable = Worksheets("Positions").Range("C9").PivotTable
pvtTable.RefreshTable

And this code does it too..

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

 

[Rofeu]

Hi,

that means that the region the pivot table occupies is expanding to include cells that already have data in them.

Defaulting to yes will result in data loss, but can be accomplished by turning off alerts:

Application.DisplayAlerts = false

Do not forget to turn it back on at the end of your code.

A safer and better option in my opinion is making sure that no data exists in the cells where the pivot might expand to.

Cheers,

Roel