Some of you may recall my various questions re: unhiding columns & rows.
Now that I have a code that works on Worksheets("PayPeriod_01"),
I'd like to have the code respond to each of several sheets. For the
purposes of our 2 week pay period, there would be 26 or so pay periods.
Rather than have to copy the code 25 times, and doing a search / replace to
change the sheet number, is there a way of having the one code block be
called by a copy of the button, but use a variable on the sheet ( 01 , 02,
03, ... 25, 26 ) to ...
1) change the sheet name ( PayPeriod_01 , PayPeriod_02 , PayPeriod_03,
... )
2) change the range name that determines the rows to open for that sheet
( Empls_Per_01, Empls_Per_02, Empls_Per_03, ...)
3) change the range name that determines the columns to open for that sheet
( Jobs_Per_01, Jobs_Per_02, Jobs_Per_03, ... )
I would guess that each sheet would have a cell with its' number ( 01, 02,
03 ... )
Or should a new button on each sheet define the 3 variables, and then call
the rest of the code?
Thanks for the help.
Brian
=====================================================
My complete code to date ...
Private Sub CommandButton1_Click()
'This part opens 3 rows for each employee, in 1st & 2nd weeks
Dim RowRange As Range
Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
Set RowRange = Worksheets("Select_Employees").Range("Empls_Per_01")
For r = 1 To 19
If UCase(RowRange.Cells(r, 1)) = "X" Then
r1 = 48 + (2 * r)
r2 = 48 + (2 * r) + 2
r3 = 298 + (2 * r)
r4 = 298 + (2 * r) + 2
Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False
End If
Next
'This part opens 2 columns for each job in progress
Dim ColRange As Range
Dim c As Long
Dim c1 As Integer
Set ColRange = Worksheets("Select_Jobs").Range("Jobs_Per_01")
For c = 1 To 100
If UCase(ColRange.Cells(c, 1)) = "X" Then
c1 = 3 + (2 * c)
Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden = False
End If
Next c
End Sub