macro to hide rows across several worksheets fails

S

SandyR

I recorded a macro by selecting a bunch of worksheets, then selecting a range
of rows, then going to the format menu and selecting column hide. When I
carry out this procedure by hand it works fine, but when I run the macro, it
only hides the rows in the first worksheet. Anyone know why or what I might
be doing wrong.

Thanks!
 
A

Aussie CPA

Sandy,
You could try adding the following code into your macro:

Dim Sheetnumber
Sheetnumber = 1
Do
"INSERT EXISTING MACRO CODE HERE"
Sheetnumber = Sheetnumber + 1
Loop Until Sheetnumber = ActiveWorkbook.Worksheets.Count

This code will run the code on each sheet in the active workbook.
 
S

SandyR

That seems like a good work around, and I will probably use it, but I would
like to understand why this doesn't work as is. I am still trying to
understand the underlying principles of this object oriented stuff.
 
S

SandyR

Here is one of my attempts. Note that there are other worksheets in the
workbook that are not selected, and that the range and clear statements work
for all the selected worksheets:

Sub setup()
'
' setup Macro
' Macro recorded 9/28/2005 by SR
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Sheets(Array("0110", "0120", "0130", "0141", "0142", "0143", "0144",
"0145", "0146", _
"0147", "0160", "0170")).Select
Sheets("0110").Activate
Columns("A:AB").Select
Selection.EntireColumn.Hidden = False
Rows("1:55").Select
Selection.EntireRow.Hidden = False

' Clear values in estimated total expenditure, level A, level B, department
request
' manager reccomend and council approved columns

Range("K8:K15").Select
Selection.ClearContents
. . . (more select and clear statements)

Rows("28:36").Select
Selection.EntireRow.Hidden = True
 
P

PCLIVE

Sorry Sandy,

It looks like VBA does not work with grouped worksheets. See Tom's reply
below:
----------------------------------------------------------------------------------

for the most part, VBA does not support actions performed on grouped sheets.
In some cases, you can work around it by using Selection, but it appears
that is not the case for you. Just loop through the sheets and perform the
action.
 
Top