Does creating a new worksheet = worksheet_change event for all others?

[Maccer]

Hi all,

I have a spreadsheet with ~40 tabs. Each tab has a couple of pivot charts which are set to refresh using the worksheet_change sub. The code I have within each tab is as follows;

Private Sub Worksheet_Change(ByVal Target As Range)


   'If data on this worksheet changes, refresh the pivot tables
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    ActiveSheet.PivotTables("PivotTable2").RefreshTable
    ActiveSheet.Protect AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True, AllowUsingPivotTables:=True
    Application.EnableEvents = True


End Sub

The issue I have is that as more tabs are added the time taken to add each new tab is increasing, up to the point where it takes a minute or so to add a new tab. I suspect that each new addition is forcing a refresh of all pivot tables in all tabs, hence the fairly linear increase in time from <5 tabs upto >40.
Is this what is happening? If so can I change the code so this no longer happens?

 

[Norie]

How are you adding the new sheets?

 

[Maccer]

Hi, I'm adding sheets via the insert-worksheet within excel. When setting up a from scratch workbook I have a macro that takes a Template tab and replicates it using move/copy (create a copy) function within excel. This is where I observed the slowdown - it ran quickly for the first few tabs then slowed down progressively.

 

[Maccer]

I also have the same amount of slowdown when removing or moving a tab - there must be some background calculation going on but I cannot think what.
I have just tried disabling events and calculations then inserting a sheet and it still takes the same amount of time, so perhaps not event related?

 

[Norie]

The only thing I can think of that would be happening when you add a new sheet is that for some reason that's triggering calculation.

However calculating won't trigger a change event.

Are you sure the Change event(s) are actually the problem? Are they even getting triggered?

You can check for that if you put a breakpoint on the header of the subs using F9.

PS Why are you referring to ActiveSheet in the code? To refer to the worksheet the code is for change ActiveSheet to Me.

Private Sub Worksheet_Change(ByVal Target As Range)


   'If data on this worksheet changes, refresh the pivot tables
    Application.EnableEvents = False
    Me.Unprotect
    Me.PivotTables("PivotTable1").RefreshTable
    Me.PivotTables("PivotTable2").RefreshTable
    Me.Protect AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True, AllowUsingPivotTables:=True
    Application.EnableEvents = True


End Sub

 

[Maccer]

I tested with the break added to one of the worksheets and it appears it is not event related. Not seen the use of Me before (I'm pretty new to vba) - Activesheet was the only way I could think of.

So now pretty stumped! It must be calculation related, but something that happens even with automatic calculation turned off...

 

[Norie]

Could it just be the tables are being refreshed, without the code, and that's taking a bit of time?

What happens if you manually turn off events by entering this in the Immediate Window and then add a new sheet?

Application.EnableEvents = False

PS Are the tables using an external data source?

 

[Maccer]

I have tried with events disabled, same issue. The pivottables are using data within the tab they are located on. I do have external data sources which are used by the formula within each tab, but I've also tried with a copy of the file and all the links broken, same issue!