VB code to reference a series of worksheets

[bbannock]

Hi everyone.

I'm looking to write code for some macros that I'm creating, the only problem is that the way I'm doing it now is very tedious. I am manually changing the macro so that id references every tab individually. I'm wondering if there is a way to reference the series of sheets as a whole. Here are two examples of the macros...

-----------------------------------------------------------

1)

Sub RefCompData()
X = EssVRetrieve("Sheet 1", Null, 1)
X = EssVRetrieve("Sheet 2", Null, 1)
If X = 0 Then
MsgBox ("REFRESH SUCCESSFUL&quot
Else
MsgBox ("REFRESH FAILED - TRY AGAIN&quot
End If
Calculate
End Sub

2)

Sub HIDEROWS()
'
' Macro1 Macro
' Macro recorded 09/11/2003 by A953997
'

'
Sheets("Sheet 1&quot.Select
range("P1&quot.Select
range(Selection, Selection.End(xlDown)).Select
For Each X In Selection
If ActiveCell.Value = "HIDE" Then
Selection.EntireRow.Hidden = True
End If
If ActiveCell.Value = "SHOW" Then
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(1, 0).Select
Next X
range("B10&quot.Select

Sheets("Sheet 2&quot.Select
range("P1&quot.Select
range(Selection, Selection.End(xlDown)).Select
For Each X In Selection
If ActiveCell.Value = "HIDE" Then
Selection.EntireRow.Hidden = True
End If
If ActiveCell.Value = "SHOW" Then
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(1, 0).Select
Next X
range("B10&quot.Select
End Sub

-----------------------------------------------------------

Please let me know if there is an easier way to do this...otherwise I'll be writing a lot of unecesarry code.
Thanks alot,
Brandon

 

[dyarwood]

You could reference the sheet as follows

i = 1
Do While i < last_sheet_number
sheet_ref = &quot;Sheet &quot; & i 'Sheet 1

Sheets(sheet_ref).Select
range(&quot;P1&quot.Select
range(Selection, Selection.End(xlDown)).Select
For Each X In Selection
If ActiveCell.Value = &quot;HIDE&quot; Then
Selection.EntireRow.Hidden = True
End If
If ActiveCell.Value = &quot;SHOW&quot; Then
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(1, 0).Select
Next X
range(&quot;B10&quot.Select



i=i+1

Loop

If the code for all the sheets is the same you can just reference the sheet as above and loop through all the sheets.

 

[bbannock]

I was looking for somethign similar to the code I already head, however instead of writing &quot;sheet 1&quot; I would insert the range of sheets

ie. sheet 1:sheet6

 

[dyarwood]

The code I have posted will work through sheet 1 then sheet 2 .... until sheet 6 has been done. Is this not what you wanted?