Array Sheet

[Partjob]

Hi all
I can get the following sheet array by using the macro recorder.

Sheets(Array("EPN SUMMARY", "Dashboard", "Ian Cameron", "Vladimir", "Ken Golding", _
        "Andy Billings", "Stuart Weatherston", "Paul Symonds", "Peter Ball", "Ivan Key", _
        "PM2", "PM3", "PM4", "PM5")).Select

I need this line of code to be be more dynamic. The sheet names can change and can be added to.
I need a line to create a sheet array that includes all sheets except the sheet named "Dashboard" and sheet the named "EPN SUMMARY" or better still check if the sheet has the word "SUMMARY" in the name and exclude that from the array as I would use this in more than one workbook.

Thanks as always for your help
Partjob

 

[SteveO59L]

What are you trying to do ?

Could you not loop through all sheets except certain ones ?

 

[Partjob]

Hello again
I am really struggling now!
I am trying to hide Columns based on date. The end result should be a 6 or 7 month window in a 18 month spread.
I can make this work on a loop through all sheets I was only trying to speed things up. I call the following on an open event
I don't realy understand what I am doing wrong.
I can record the hiding of columns across multiple sheets, but it does not work when the code is run.

Sub ProtectWB()
Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
    For Each wSheet In Worksheets
        wSheet.Protect Password:="steve", _
            UserInterFaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True _
            , AllowFormattingCells:=True, AllowFiltering:=True
            Columns("F:Z").EntireColumn.Hidden = False
             Next wSheet
    
    'If wSheet.Name <> "Dashboard" Then
        Dim x As Range, z As Date, a As Integer
        z = DateAdd("m", -3, Date) 'date 3 months in past
             For Each x In Sheets("EPN SUMMARY").Range("F3:Z3")
            If x.Value > z Then 'check first column that is to be visible
                a = x.Column
                Sheets(Array("EPN SUMMARY", "Ian Cameron", "Vladimir", _
                "Ken Golding", "Andy Billings", "Stuart Weatherston", _
                "Paul Symonds", "Peter Ball", "Ivan Key", "PM2", _
                "PM3", "PM4", "PM5")).Select
            Sheet14.Activate
                If a = 6 Then
                Range(Columns(12), Columns(24)).EntireColumn.Hidden = True
                a = 100 'signal to exit
                Else
                    If a > 6 And a < 18 Then
                    Range(Columns(6), Columns(a - 1)).EntireColumn.Hidden = True
                    Range(Columns(a + 7), Columns(24)).EntireColumn.Hidden = True
                    a = 100 'signal to exit
                    Else
                    Range(Columns(6), Columns(17)).EntireColumn.Hidden = True
                    a = 100 'signal to exit
                    End If
                End If
            
            End If
            If a = 100 Then
            Sheet1.Select
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
            End If
        Next x
    'End If
   
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

I need guidance on this, thank you.
Partjob

 

[Partjob]

I have gone back to looping through on this, a bit juggling the loops around has speeded things up.
Unless anyone can suggest a way of making this more efficient, then I will live with this.

Sub ProtectWB()
Dim wSheet As Worksheet, x As Range, z As Date, a As Integer
z = DateAdd("m", -3, Date) 'date 3 months in past
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each x In Sheets("EPN SUMMARY").Range("F3:Z3")
            If x.Value > z Then 'check first column that is to be visible
                a = x.Column
              
    For Each wSheet In Worksheets
        wSheet.Protect Password:="steve", _
            UserInterFaceOnly:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True _
            , AllowFormattingCells:=True, AllowFiltering:=True
 
    If wSheet.Name <> "Dashboard" Then
        
                wSheet.Select
                Columns("F:Z").EntireColumn.Hidden = False
                If a = 6 Then
                
                Range(Columns(12), Columns(24)).EntireColumn.Hidden = True
               
                Else
                    If a > 6 And a < 18 Then
                    Range(Columns(6), Columns(a - 1)).EntireColumn.Hidden = True
                    Range(Columns(a + 7), Columns(24)).EntireColumn.Hidden = True
                    
                    Else
                    Range(Columns(6), Columns(17)).EntireColumn.Hidden = True
                    
                    End If
                End If
            
            End If
     
    Next wSheet
    End If
    If a > 0 Then
            Sheet1.Select
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
            End If
     Next x
End Sub

Thanks for the imput.
Partjob