Making the code run on multiple sheets

[Junior19]

I'm currently updating a rotation plan, and we've opted to go for one sheet pr week, a total of 52 sheets + 2, since our 9 week rotation plan does not add up in 52 weeks.
Thus, I have sheest 1-54, a base-sheet containing static information, and a information page.

On each of the sheets I have the identifier of the slot in B5 - B23, then weekdays from monday to friday in row C - I, so that it becomes a grid showing who is working when.

As I have a number of unused slots in our plan I would like to hide the rows containing a set value in a given column, and I've found a piece of code which allows me to do just that: (borrowed from http://excel.tips.net/Pages/T001940_Hiding_Rows_Based_on_a_Cell_Value.html)

Sub HURows()
BeginRow = 6
EndRow = 23
ChkCol = 2


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 10 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

So basically I've given the unused slots in our plan numbers below 10, and this works fine.
However, the code does only run on one sheet at a time, and with 54 sheets it takes quite a bit of time to manually go through each and every one of them and run the code.

How can I modify this code so that it first selects sheets 1-54, then runs on each of those sheets? (but preferrably not on the base-sheet and the info-sheet)

Or, if there is a piece of better code that will do the same job, that would be fine too...

 

[Trevor G]

Look at this code it is just checking the first 4 sheets but you can amend it to 54 if required. Make sure the module sheet doesn't have Option Explicit at the top

Sub HideRows()
For i = 1 To 4
Worksheets(i).Select
BeginRow = 10
EndRow = 100
ChkCol = 7
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
Next i
End Sub

 

[Junior19]

Look at this code it is just checking the first 4 sheets but you can amend it to 54 if required. Make sure the module sheet doesn't have Option Explicit at the top

Just tested your solution, and it works!
Thanks, that's really appreciated!

 

[Trevor G]

Pleased to read you have what you now need.