On Sun, 10 Apr 2011 14:50:19 +0100, Walter Briscoe wrote:

In message of Sun, 10 Apr
2011 06:29:00 in microsoft.public.excel.programming, Ron Rosenfeld
writes
On Sun, 10 Apr 2011 08:48:44 +0100, Walter Briscoe
wrote:


[snipped why "Dim S as Sheet1: For Each S in Sheets ..." gets 1038]


I don't have a particular citation, but I'd wonder if what you are
seeing has to do with the particular type of object that is Sheet1.
This is not specifically defined as part of the Sheets collection. And
it also seems to me that there can only be a single Sheet1 object in a
workbook; so you really couldn't iterate through all of the Sheet1's,
since there is only one.

Agreed!


If you want to iterate through all the worksheets in a workbook, you
could use Dim S as Worksheet.

I should have seen that. I did not read Sheets Collection Object Help.


If you want to iterate through all of the sheets in a workbook,
regardless of the type of sheet, you could do something like:

dim i as long
for i = 1 to Sheets.count
...do something to the sheet...
next i

I am happy to do Dim S as Worksheet: for Each S in sheets ...
[If sheets contains any charts, I do not understand the data.]

Thanks.


Glad to help. Thanks for the feedback.