Multi Sheet select and hiderows
 

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

Multi Sheet select and hiderows
 

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

Mike F
"ozcank" wrote in
message ...

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

Multi Sheet select and hiderows
 

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


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=381906