Selected Answer
Hi Cathy,
The 3-D action the Select object is able to perform for the user isn't available to VBA, nor is it required. You can just loop through all sheets and do your adjustments on each of them. You would do better if you would avoid the Select object entirely unless you really want to make a selection for the user. For all manipulations use the Range object instead.
I'm impressed with the progress you are making. Therefore I took the time to re-write your code the way I would have programmed the same task and added comments. Ignore the following if you aren't interested.
Sub Col_Adj()
' 12 Dec 2018
Const Selector As String = "P01 Oct" ' change the month here (not in the code!)
Dim Wb As Workbook ' I recommend to declare the workbook
Dim Ws As Worksheet
Dim FirstClm As Long, LastClm As Long
Dim Act As Boolean
Dim C As Long
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets("Summary") ' and declare the worksheet (while needed)
Ws.Select ' after this there is no more need for it
Act = (Ws.Range("ActPd").Value = "P00") ' not clear from your code where "ActPd" is located
' The following is probably not necesary (but your code has this condition):-
' Using StrComp makes the comparison case-insensitive in case of typos
If (Not Act) And _
(StrComp(Ws.Range("ActPd").Value, Selector, vbTextCompare) <> 0) Then _
Exit Sub
FirstClm = Columns("G").Column ' I would assign the value directly = 7
LastClm = Columns("AD").Column ' I would assign the value directly = 30
Application.ScreenUpdating = False
For Each Ws In Sheets
' the Summary sheet isn't excluded from the following action
If Ws.Visible Then
' obviously, this would be simpler if you could
' keep the columns together in contiguous blocks:-
For C = FirstClm To LastClm
Ws.Columns(C).EntireColumn.Hidden = Act
Act = Not Act
Next C
End If
Next
Application.ScreenUpdating = True
End Sub
The main difference is that I pay much more attention to declarations, especially the objects. I think you will also like the way I designed the column selection (although, of course, they need no selection, just addressing). The simple logic is that you need every other column to be hidden, with a different state for the first column, depending upon the range "ActPd". So, instead of instructing which columns to hide, I concentrated the effort on setting the correct value for the first column and then created an alternating sequence to follow.
Observe that I turn off ScreenUpdating before the action starts and leave it off until the end. That speeds up the code significantly! Note that you can't Select anything on sheets that aren't active. The good news is that you don't need to select anything. It's all demonstrated in the attached workbook.
