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

ajmckenna

Board Regular
Joined
Oct 7, 2002
Messages
145
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Something like:

Code:
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
<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

Board Regular
Joined
Oct 7, 2002
Messages
145
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

This will act on all visible sheets at once:

Code:
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

Board Regular
Joined
Oct 7, 2002
Messages
145
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,144,122
Messages
5,722,604
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top