Run VBA Code on Active Worksheet Only - Not All Worksheets

[ML43s52]

The code below runs fast and as expected when used in a workbook with a single worksheet.
The code below takes forever to execute when used in a workbook that contains 50 of these worksheets all with this same code.
It appears that the code executes on all worksheets when activated on the active worksheet.
I only want the code to execute on the active worksheet.
What changes do I need to make?

Private Sub Worksheet_Calculate()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
Static oldval
If ws.Range("$CA$17").Value <> oldval Then
oldval = ws.Range("$CA$17").Value

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
ws.Unprotect "2025"
Dim C As Range
ws.Range("A:AFS").EntireColumn.Hidden = False
For Each C In ws.Range("A391:AFS391").Cells
If C.Value = 0 Then
C.EntireColumn.Hidden = True
End If
Next C
ws.Range("BB1").AutoFilter Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2
ws.Protect Password:="2025", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables _
:=True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End If
End Sub

 

[Joe4]

I am not sure I follow.

The "Worksheet_Calculate" event procedure code is placed in the specific sheet modules that you want it to run against (it is not placed in the "ThisWorkbook" module or General modules).
By definition, it only runs against the sheet whose module it is placed in.

If you wanted this to run against "Sheet1", you would place it in the "Sheet1" module in VBA.
If you wanted it to also run against another sheet (i.e. "Sheet3"), then you would place it in that particular Sheet module in VBA (i.e. "Sheet3").
So you only place the code in the sheet modules for the sheets that you want it to run against (it will NOT run against all sheets).

However, keep in mind that it runs automatically when any sort of calculation happens on that sheet.
Let's say that the code exists in Sheet1. And in cell A1 on Sheet1, you have a formula that pulls a value from Sheet2, Range A1.
So if you update the value in cell A1 on Sheet2, that will cause the value in cell A1 on Sheet1 to be re-calculated, which would fire off the code.

Likewise, if you have any VBA code that causes a value on Sheet1 to change or be recalculated, it will cause that code to fire.

 

[Anthony47]

I understood that each of 50 the sheets contains the code. In this situatione if the sheets contain any formula that uses "volatile" functions (as Now, Today, Offset, Indirect, Rand, Cell to name those that I use quite often) then all the 50 Calculate events will trigger and will take their time.
What is silly is that (I seem) all the codes will revork the same active worksheet and probably they will not excecute what they were intended to execute because it compares oldval, set on the latest change in the "last activesheet", with "$CA$17" of the current active sheet...

As a bandaid I suggest adding this instrucion in this position:

Private Sub Worksheet_Calculate()
Dim ws As Worksheet
If ActiveSheet.Name <> Me.Name Then Exit Sub     '<<< ADD THIS LINE (on each of the Sub Worksheet_Calculate)
'...

 

[ML43s52]

I understood that each of 50 the sheets contains the code. In this situatione if the sheets contain any formula that uses "volatile" functions (as Now, Today, Offset, Indirect, Rand, Cell to name those that I use quite often) then all the 50 Calculate events will trigger and will take their time.
What is silly is that (I seem) all the codes will revork the same active worksheet and probably they will not excecute what they were intended to execute because it compares oldval, set on the latest change in the "last activesheet", with "$CA$17" of the current active sheet...

As a bandaid I suggest adding this instrucion in this position:

Private Sub Worksheet_Calculate()
Dim ws As Worksheet
If ActiveSheet.Name <> Me.Name Then Exit Sub     '<<< ADD THIS LINE (on each of the Sub Worksheet_Calculate)
'...

Your band aid suggestion worked like a charm, thank you for the input, greatly appreciated.

 

[Anthony47]

Thank you for the feedback