doing the commented out stuff below doesn't work. i will be adding
worksheets and so i want to have a collection to start with and just use
those thru my code.

Dim startSheets As Worksheets
Dim sheet As Worksheet


'Set startSheets = ActiveWorkbook.Worksheets

'For Each sheet In ActiveWorkbook.Worksheets
' startSheets.Add (sheet)
'Next

thanks

Change
Dim startSheets As Worksheets
to
Dim startSheets As Sheets

Then Set startSheets = ActiveWorkbook.WorkSheets will work.
The subsequent loop will still throw a run time error - but it isn't
needed since the sheets in question are already part of the collection.
You don't need additional code to initialize startSheets. To verify
this you can replace the loop with

For Each sheet In startSheets
MsgBox sheet.Name
Next

and give it a whirl.

The strange truth behind all this seems to be that there isn't any such
thing as a Worksheets object - even though you can declare a variable
to be one!

Hope that helps

-John Coleman

TS wrote:
> doing the commented out stuff below doesn't work. i will be adding
> worksheets and so i want to have a collection to start with and just use
> those thru my code.
>
> Dim startSheets As Worksheets
> Dim sheet As Worksheet
>
>
> 'Set startSheets = ActiveWorkbook.Worksheets
>
> 'For Each sheet In ActiveWorkbook.Worksheets
> ' startSheets.Add (sheet)
> 'Next
>
> thanks

thanks a lot, that worked

"John Coleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Change
> Dim startSheets As Worksheets
> to
> Dim startSheets As Sheets
>
> Then Set startSheets = ActiveWorkbook.WorkSheets will work.
> The subsequent loop will still throw a run time error - but it isn't
> needed since the sheets in question are already part of the collection.
> You don't need additional code to initialize startSheets. To verify
> this you can replace the loop with
>
> For Each sheet In startSheets
> MsgBox sheet.Name
> Next
>
> and give it a whirl.
>
> The strange truth behind all this seems to be that there isn't any such
> thing as a Worksheets object - even though you can declare a variable
> to be one!
>
> Hope that helps
>
> -John Coleman
>
> TS wrote:
>> doing the commented out stuff below doesn't work. i will be adding
>> worksheets and so i want to have a collection to start with and just use
>> those thru my code.
>>
>> Dim startSheets As Worksheets
>> Dim sheet As Worksheet
>>
>>
>> 'Set startSheets = ActiveWorkbook.Worksheets
>>
>> 'For Each sheet In ActiveWorkbook.Worksheets
>> ' startSheets.Add (sheet)
>> 'Next
>>
>> thanks
>