How do I suppress Excel message for any pivot tabl

Question

I have created a pivot table to summarize some information and have added some formulas adjacent to the pivot table to do calculations on the numbers included in it. I have created a macro that re-enters the formulas whenever the user changes the size of the pivot table (in the PivotTableUpdate event) by showing or hiding various rows/columns of data.

My problem is that whenever columns of data are added to the pivot table, it asks me "Do you want to replace the contents of the destination cells?" I always click yes, because although the cells will be overwritten when the pivot table expands, the formulas will be re-entered in their correct cell and everything is fixed and formatted properly by the macro.

Therefore, I would like to know where I should put application.displayalerts = false so that it is effective to suppress the message box whenever the user expands the pivot table.

Answer 1

Hi tlewis3348 i think this is what you are looking for

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

Answer 2

FWIW This solution worked great for me. I just activated each sheet individually and called the UpdateIt function before doing anything else, e.g.,

Worksheets("RFS Monthly Summary").Activate
UpdateIt
Worksheets("RFS Daily Activity").Activate
UpdateIt