Use String in place of Object

[edd189]

I have the following code I want to simplify using a loop statement:

Call HideUnusedHeader(Sheet3, 13)
Call HideUnusedHeader(Sheet4, 51)
Call HideUnusedHeader(Sheet5, 89)
Call HideUnusedHeader(Sheet6, 127) etc etc

The HideUnusedHeader is as follows:

Sub HideUnusedHeader(SheetName As Object, Row As Integer)
If Sheets(SheetName.Name).Range("K49").Value = 0 Then
Sheets("Master Printout").Rows(Row).EntireRow.Hidden = True
End If
End Sub

Is there a way I can say "Sheet"+i inplace of Sheet3
In other words, I want to basically convert a string to an object nam
and use that string to call the object.

Any help would be appreciated.

Thanks,
Edd Lovette
(e-mail address removed)

 

[Frank Kabel]

Hi
try something like
sub foo
dim i
for i = 1 to 10
worksheets("sheet" & i).activate
msgbox activesheet.range("A1").value
next i
end sub

 

[Bob Phillips]

Hi Edd,

I think this might be it

Dim aryRows
Dim arySheets
arySheets = Array(3, 4, 5, 6)
aryRows = Array(13, 51, 89, 127)

For i = LBound(arySheets,1) To UBound(arySheets,1)
Call HideUnusedHeader("Sheet" & arySheets(i), aryRows(i))
Next i

Sub HideUnusedHeader(SheetName As string, Row As Integer)
If Sheets(SheetName).Range("K49").Value = 0 Then
Sheets("Master Printout").Rows(Row).EntireRow.Hidden = True
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[edd189]

Here is the answer I came up with based on the responses.


Dim i As Integer
i = 0
Do While i < (Worksheets.Count - 2)
Call HideUnusedHeader(i + 3, i * 38 + 13)
i = i + 1
Loop

Sub HideUnusedHeader(SheetNumber As Integer, Row As Integer)
If Sheets(SheetNumber).Range("K49").Value = 0 Then
Sheets("Master Printout").Rows(Row).EntireRow.Hidden = True
End If
End Sub




I found out that you can call a worksheet with its title or with it
reference number. So sheets("sheet1") has the same function a
sheets(1).

Thanks,
Ed