Prevent workbook open event from triggering worksheet code

[VeryForgetful]

Hi,

I use the following code to refresh each PivotTable in my workbook on the open event. The file I am using acts as a template to copy data to, so basically it checks if the template is empty on open and if it is it prevents the tables from refreshing, thus avoiding the "Your source data requires at least 2 rows of data" warning.

The problem i'm having is that as these tables are refreshed on open it is calling the worksheet code as well. I have code on these worksheets for Worksheet_Activate, Worksheet_Change and Worksheet_PivotTableUpdate.

I want this initial code to only refresh the tables. The other worksheet code is for something that I need to do later on (i.e. after the initial refresh).

I don't want to save the cache with the tables as it will bloat the workbook size.

Private Sub Workbook_Open()

If Sheets("Data").Range("A2").Value <> "" Then

    Dim pt As PivotTable
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
    For Each pt In WS.PivotTables
    pt.RefreshTable
    Next pt
    Next WS

End If

Worksheets("Summary").Select

End Sub

Thanks

 

[Domenic]

The EnableEvents property of the Application object can be used...

Private Sub Workbook_Open()

    If Sheets("Data").Range("A2").Value <> "" Then
        [COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
        Dim pt As PivotTable
        Dim WS As Worksheet
        For Each WS In ThisWorkbook.Worksheets
            For Each pt In WS.PivotTables
                pt.RefreshTable
            Next pt
        Next WS
        [COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
    End If
    
    Worksheets("Summary").Select

End Sub

Hope this helps!

 

[VeryForgetful]

Thanks Domenic, that's sorted it

 

[Domenic]

You're very welcome!