Run Macro on various other sheets

[Leonvl]

I want to run a script when leaving a worksheet.
When I put the following code the code runs in the current sheet and not in the targeted sheets. What's wrong...?

Sub Workbook_Deactivate()
    Dim ws As Worksheet
    Dim x As Long
    
    Application.ScreenUpdating = False
    Application.enableEvents = False
    
    For Each ws In ActiveWorkbook.Worksheets
      If ws.Name Like "CF*" Or ws.Name Like "Scenario*" Then
        ws.Activate
        Application.Run "HideColX"
        Application.Run "HideRowX"
      End If
      Next ws
    
    Application.enableEvents = True
    Application.ScreenUpdating = True


End Sub
Private Sub HideColX()
Dim x As Long
For x = 3 To 47
    If UCase(Cells(1, x).Value) = "X" Then
        Columns(x).Hidden = True
    Else
        Columns(x).Hidden = False
    End If
Next
End Sub
Private Sub HideRowX()
Dim x As Long
For x = 5 To 47
    If UCase(Cells(x, 1).Value) = "X" Then
        Rows(x).Hidden = True
    Else
        Rows(x).Hidden = False
    End If
Next
End Sub

 

[JoeMo]

What's the name of the current sheet?

 

[Leonvl]

The current sheet name is "Project Setup"

 

[JoeMo]

The current sheet name is "Project Setup"

Your event code triggers when you deactivate the workbook it is in. Given that it's not active, you can't activate a sheet in it. Untested but see if this works:

Sub Workbook_Deactivate()
    Dim ws As Worksheet
    Dim x As Long
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each ws In Me.Worksheets
      If ws.Name Like "CF*" Or ws.Name Like "Scenario*" Then
        Application.Run "HideColX"
        Application.Run "HideRowX"
      End If
      Next ws
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub