Is there any way to scroll thru all the spreadsheets in a
workbook without referencing them by name

Well, its a bit vague your question but i would suggest that scrolling
through by name is one of the least efficient ways...

Try one of these...

''This one will loop through all Worksheets in a workbook
Dim objSht as Sheet
For each objSht in ThisWorkbook.Worksheets
.....yuor code here
Next objSht

But, if you have Chart sheets as well and you want to loop through them
as well as worksheets then you need to take a slightly different
approach I think...

Dim intShtCount As Integer
For intShtCount = 1 To ThisWorkbook.Sheets.Count
'''Your code here
Next intShtCount


Hth,
OJ

I'm not sure what your code is doing as it doesn't seem to
be doing anything to the worksheets, I get the eror
message user defined type not defined

also can you make each sheet the active sheet as it moves
thru the code

here is the code as I put it in
Dim objSht as Sheet
For each objSht in ThisWorkbook.Worksheets

Application.Run Macro:=("EssMenuRetrieve")
Next objSht


-----Original Message-----
Well, its a bit vague your question but i would suggest
that scrolling
through by name is one of the least efficient ways...

Try one of these...

''This one will loop through all Worksheets in a workbook
Dim objSht as Sheet
For each objSht in ThisWorkbook.Worksheets
.....yuor code here
Next objSht

But, if you have Chart sheets as well and you want to
loop through them
as well as worksheets then you need to take a slightly
different
approach I think...

Dim intShtCount As Integer
For intShtCount = 1 To ThisWorkbook.Sheets.Count
'''Your code here
Next intShtCount


Hth,
OJ

.

Sorry...typo...

Should have read ....

Dim objSht as Worksheet not Dim objSht as Sheet....


Nigel, can I suggest we close this thread and continue your other post?

Thanks,
OJ

You can use a For/Each loop

Dim WS As Worksheet

For Each WS in ThisWorkbook.Worksheets
Debug.Print WS.Name
Next WS

or, since worksheets also have and Index property, you can write

With ThisWorkbook
For S = 1 To .Worksheets.Count
Debug.Print .Worksheets(S).Name
Next S
End With

or
On Tue, 15 Mar 2005 19:15:32 -0800, "Nigel Bennett"
wrote:

Is there any way to scroll thru all the spreadsheets in a
workbook without referencing them by name

Well I am almost there excep for one little issue, the
final portion of the code which is meant to resize the
columns even though it is within the in statement only
works on the menu screen

Dim value1, value2, value3 As String
Dim LastCol As Range
Dim oCell As Range
value1 = Sheets("Menu").Range("G22")
value2 = Sheets("Menu").Range("G23")
value3 = Sheets("Menu").Range("G24")
'this Code will allow the user to automatically refresh
multiple sheets on a spreadsheet
'without having to go to each one individually
Application.ScreenUpdating = True
Dim objWs As Worksheet, intInc As Integer

For Each objWs In ThisWorkbook.Worksheets

objWs.Activate
With objWs
If Not .Name = "Menu" Then
.Cells(2, 2).End(xlToRight).Offset(0, 1) =
value1
.Cells(3, 2).End(xlToRight).Offset(0, 1) =
value2
.Cells(4, 2).End(xlToRight).Offset(0, 1) =
value3
Application.Run Macro:=("EssMenuRetrieve")


Set LastCol = Cells(2, Columns.Count).End
(xlToLeft)
Set rng = Range(Cells(2, 2), LastCol)

For Each oCell In rng
If oCell = "" Then
oCell.ColumnWidth = 12

Else
oCell.ColumnWidth = 1

End If
Next oCell
End If


End With
Next objWs

Nigel,
a couple of points..

1. Dim value1, value2, value3 As String ''This line will only dim
value3 as a string, the others will be variant. Should read:

Dim Value1 as String, Value2 as String, Value3 as String

2. You need to qualify these statements with a sheet....since you are
already referencing a sheet with the With Statement then try this

For Each objWs In ThisWorkbook.Worksheets
With objWs
If Not .Name = "Menu" Then
.Activate
.Cells(2, 2).End(xlToRight).Offset(0, 1) = value1
.Cells(3, 2).End(xlToRight).Offset(0, 1) = value2
.Cells(4, 2).End(xlToRight).Offset(0, 1) = value3

Application.Run Macro:=("EssMenuRetrieve")

Set LastCol = .Cells(2, .Columns.Count).End(xlToLeft)
Set rng = .Range(.Cells(2, 2), LastCol)


For Each oCell In rng
With oCell
If .Value = "" Then
.ColumnWidth = 12
Else
.ColumnWidth = 1
End If
End With
Next oCell
End If
End With
Next objWs

Hth
OJ