Multi Sheet select and hiderows

[ozcank]

Hi All

Ok, I'm stcuk with a particularly strange thing. I'm new to VBA so please forgive me. I have around 12 sheet, all containing exactly same format. The only thing that changes are the names in column A and the values in other columns. What I want is to be able to hide all the rows beneath the data rows that contain no data. At the bottom of each sheet is the grand total in row 45, so i want this to stay.

I can get it to work for the 1st sheet with the code


Option Explicit
Public Cell As Integer
Public Column1 As String

Public Sub HideSheets()

Application.ScreenUpdating = False

'unhide all cells in Qtr and Qtr YTD sheets
Sheets("GRP 01").Select
Sheets("GRP 01").Activate
Rows("10:45").Select
Selection.EntireRow.Hidden = False

Call HideRows

End Sub

Public Sub HideRows()

'Hide 0 rows in first Section
Column1 = "A"
Cell = 10
Do Until Range(Column1 & Cell).Value = ""
Cell = Cell + 1
Loop
Rows(Cell & ":43").Select
Selection.EntireRow.Hidden = True

End Sub


where rows 10:45 is the data rows. But how would I go about doing the same for other sheet, which are called GRP 02, GRP 03 etc ???
Ideally, I would want the macro to go to each sheet and carry out the same operation. Any ideas?

Many thanks in advance

Oz

[Mike Fogleman]

Dim w As Worksheet
For Each w In Worksheets
'Do your stuff
Next
End Sub

Mike F
"ozcank" <ozcank.1r4nuf_1119611120.2122@excelforum-nospam.com> wrote in
message news:ozcank.1r4nuf_1119611120.2122@excelforum-nospam.com...
>
> Hi All
>
> Ok, I'm stcuk with a particularly strange thing. I'm new to VBA so
> please forgive me. I have around 12 sheet, all containing exactly same
> format. The only thing that changes are the names in column A and the
> values in other columns. What I want is to be able to hide all the rows
> beneath the data rows that contain no data. At the bottom of each sheet
> is the grand total in row 45, so i want this to stay.
>
> I can get it to work for the 1st sheet with the code
>
>
> Option Explicit
> Public Cell As Integer
> Public Column1 As String
>
> Public Sub HideSheets()
>
> Application.ScreenUpdating = False
>
> 'unhide all cells in Qtr and Qtr YTD sheets
> Sheets("GRP 01").Select
> Sheets("GRP 01").Activate
> Rows("10:45").Select
> Selection.EntireRow.Hidden = False
>
> Call HideRows
>
> End Sub
>
> Public Sub HideRows()
>
> 'Hide 0 rows in first Section
> Column1 = "A"
> Cell = 10
> Do Until Range(Column1 & Cell).Value = ""
> Cell = Cell + 1
> Loop
> Rows(Cell & ":43").Select
> Selection.EntireRow.Hidden = True
>
> End Sub
>
>
> where rows 10:45 is the data rows. But how would I go about doing the
> same for other sheet, which are called GRP 02, GRP 03 etc ???
> Ideally, I would want the macro to go to each sheet and carry out the
> same operation. Any ideas?
>
> Many thanks in advance
>
> Oz
>
>
> --
> ozcank
>
>
> ------------------------------------------------------------------------
> ozcank's Profile:
> http://www.excelforum.com/member.php...fo&userid=5328
> View this thread: http://www.excelforum.com/showthread...hreadid=381906
>

[ozcank]

Hi Mike, I'm sorry but I am quite a novice. Am I to replace the w after dim with the name of each worksheet? Again I'm sorry, I'm a beginner.

Thanks
Oz