How to select all sheets to run a macro on all sheets?

[ajmckenna]

I have a formating Macro that I want to run on all sheets. Basically the macro hides an unhides certain columns. I my problems are as follows:

1) the nummber and names of the worksheets will change often so I can't just use and arrray to select worksheets(1,3,4,5....)I have dummy sheets which are hidden and placed as the second and last worksheet so I can use a 3D sum formula across all sheets.
I think because these blank sheets are hidden I cannot select all sheets. Ideally,
I need code to select all worksheets except for hidden worksheets and the last worksheet

2) assuming I solve the above, I have tested the macro and and if I go through steps manually, i.e. group all sheets and hide say columns (b:d) these colums are now hidden on all sheets but when I do this throught the the macro it just hides the columns on the active sheet even though the macro groups the sheets. see my test code below
Sub ViewReport2()
Dim ReportKey As String
ReportKey = Range("ReportKey")

Worksheets(Array(1, 3, 4, 5)).Select
Worksheets(1).Activate
Columns("B:EQ").EntireColumn.hidden = True
If ReportKey = 1 Then
Range("B:M,EE:EE").EntireColumn.hidden = False
Range("A77").Select
Else
End If

any ideas?

 

[Andrew Poulsom]

Something like:

Dim Sh As Worksheet
For Each Sh in ThisWorkbook.Worksheets
   If Sh.Visible = True Then
      Sh.Activate
'     *** Your code goes here ***
   End If
Next Sh

 

[Ekim]

<pre>
"I need code to select all worksheets except for hidden worksheets and the last worksheet."</pre>

Try this:<pre>
Sub Test()
' Start with Sheet2 and skip the last worksheet
For i = 2 To Sheets.Count - 1

If Sheets(i).Visible = True Then

' ***** Your code here ******

End If
Next ' Get the next worksheet
End Sub</pre>

Regards,

Mike
This message was edited by Ekim on 2002-10-28 12:00

 

[ajmckenna]

Guys both of those would work, but this would cause the Macro to run numerous times)once for each sheet that is activated. Is it possible to group all these sheets and run the macro once, and thereby formating all sheets the same?
As it turns out, the array of sheets I need to select will always be the array of sheets
from Sheets(3) to Sheets(Worksheets.count -2.
sheets(2) and Sheets(Worksheets.count -1) are the hidden sheets. Also, I guess it doesn't matter if macro runs on these sheets since they are blank, but if they are excluded it is probably more efficient
Thanks

 

[Andrew Poulsom]

This will act on all visible sheets at once:

Sub Test()
    Dim x As Integer
    Dim Sh As Worksheet
    Dim ArrSh() As String
    x = 1
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Visible = True Then
            ReDim Preserve ArrSh(1 To x)
            ArrSh(x) = Sh.Name
            x = x + 1
        End If
    Next Sh
    Sheets(ArrSh).Select
    Sheets(ArrSh(1)).Activate
'   *** Your code goes here ***
    Sheets(ArrSh(1)).Select
End Sub

 

[ajmckenna]

Andrew

Thanks , this code selects proper worksheets but for some reason it only performs my code on the first worksheet. If you manually go though the steps (i.e hide column range) it works across all sheets but if you perform same functions through a macro it only updates the one active sheet. I did a test of this by recording macro
Sub columntest()
Sheets(Array("sheet1", "sheet2", "sheet3")).Select
Sheets("sheet1").Select
Columns("B:F").Select
Selection.EntireColumn.hidden = True
End Sub
only sheet one will have columns hidden

I will use your first solution, thought it would be slow but it appears to work fine

thanks again

 

[Andrew Poulsom]

That's because of your line:

Sheets("Sheet1").Select

That cancels selection of all the sheets.

In my code I just had:

Sheets(ArrSh(1)).Activate

This activates Sheet1, but all sheets remain selected. Your code should just be the hiding columns bit.