Deactivate to call macro to apply for select sheets

[margram]

Hi,

I have a front page sheet where you select a few options, which then puts "hide" or "keep" above the columns that may or may not require hiding on sheets 2 through 5. The current code i have works when you deactivate the front page sheet, and calls the macro to hide columns that have "hide" and not hide columns that have "keep". Unfortunately this code only works for the next sheet selected, and not the remaining sheets. Any help to have this run smoothly would be greatly appreciated. Total newbie over here! Thanks.

This is my frontpage worksheet deactivate code below:

Sub Worksheet_Deactivate()

Call P4CSRHide

End Sub

The module macro that the above is calling is below:

Sub P4CSRHide()


Dim c As Range
For Each c In Range("D1:Q1").Cells
If c.Value = "Hide" Then
c.EntireColumn.Hidden = True
End If
If c.Value = "Keep" Then
c.EntireColumn.Hidden = False
End If
Next c

End Sub

 

[GWteB]

Paste this in each module of sheet2 through sheet5:

Private Sub Worksheet_Activate()
    Call P4CSRHide(Me)
End Sub

and paste this in a standard module:

Public Sub P4CSRHide(ByRef argSht As Worksheet)
    
    Dim c As Range
    
    For Each c In argSht.Range("D1:Q1").Cells
        If c.Value = "Hide" Then
            c.EntireColumn.Hidden = True
        ElseIf c.Value = "Keep" Then
            c.EntireColumn.Hidden = False
        End If
    Next c
End Sub

 

[margram]

Thank you for the quick reply GWteB. My only concern with this method is that it runs a macro each time one of those sheets is activated. Meaning that the undo option is "reset" when changing sheets. I would prefer it to be run one time at the beginning or whenever the option is changed in the frontpage sheet, effectively resetting the undo option less, rather than being called multiple times.

 

[GWteB]

I see... stay with your orignal approach, using the Deactivate event of your frontpage
module sheet1:

Sub Worksheet_Deactivate()
    Call P4CSRHide
End Sub

standard module:

Public Sub P4CSRHide()
    
    Dim oSh As Worksheet
    Dim c   As Range
    
    For Each oSh In ThisWorkbook.Worksheets
        With oSh
            If (CBool(InStr(LCase("|Sheet2|Sheet3|Sheet4|Sheet5|"), LCase("|" & .Name & "|")))) Then
                For Each c In .Range("D1:Q1").Cells
                    If c.Value = "Hide" Then
                        c.EntireColumn.Hidden = True
                    ElseIf c.Value = "Keep" Then
                        c.EntireColumn.Hidden = False
                    End If
                Next c
            End If
        End With
    Next oSh
End Sub

 

[margram]

Thanks GWteB! That works perfectly!

 

[GWteB]

Welcome to the MrExcel forum!
Glad to help and thanks for letting me know.