Hi All,

I'm trying to create a new collection containing all worksheets in the
active workbook except the last one, but I failed. Please help!

Regards,
Stefi

Sub NewCollection()
Dim wsColl As Collection
Dim ws As Worksheet

Set wsColl = New Collection
For Each ws In ActiveWorkbook.Worksheets
If ws.Index <> ActiveWorkbook.Worksheets.Count Then
wsColl.Add ws.Name, ws.Name
End If
Next ws
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Stefi" <(E-Mail Removed)> wrote in message
news:A1916D63-1FCB-4032-84E5-(E-Mail Removed)...
> Hi All,
>
> I'm trying to create a new collection containing all worksheets in the
> active workbook except the last one, but I failed. Please help!
>
> Regards,
> Stefi
>

Thanks Bob! Another question: Isn't it possible to set a new collection to an
existing one, I mean something like this:

Dim wsColl As Collection
Set wsColl = ActiveWorkbook.Worksheets

Regards,
Stefi

„Bob Phillipsâ€� ezt Ã*rta:

> Sub NewCollection()
> Dim wsColl As Collection
> Dim ws As Worksheet
>
> Set wsColl = New Collection
> For Each ws In ActiveWorkbook.Worksheets
> If ws.Index <> ActiveWorkbook.Worksheets.Count Then
> wsColl.Add ws.Name, ws.Name
> End If
> Next ws
> End Sub
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "Stefi" <(E-Mail Removed)> wrote in message
> news:A1916D63-1FCB-4032-84E5-(E-Mail Removed)...
> > Hi All,
> >
> > I'm trying to create a new collection containing all worksheets in the
> > active workbook except the last one, but I failed. Please help!
> >
> > Regards,
> > Stefi
> >
>
>
>

Dim wsColl As worksheets
Set wsColl = ActiveWorkbook.Worksheets

--
Regards,
Tom Ogilvy


"Stefi" <(E-Mail Removed)> wrote in message
news:31DAAA87-C940-4F37-831B-(E-Mail Removed)...
> Thanks Bob! Another question: Isn't it possible to set a new collection to
> an
> existing one, I mean something like this:
>
> Dim wsColl As Collection
> Set wsColl = ActiveWorkbook.Worksheets
>
> Regards,
> Stefi
>
> "Bob Phillips" ezt írta:
>
>> Sub NewCollection()
>> Dim wsColl As Collection
>> Dim ws As Worksheet
>>
>> Set wsColl = New Collection
>> For Each ws In ActiveWorkbook.Worksheets
>> If ws.Index <> ActiveWorkbook.Worksheets.Count Then
>> wsColl.Add ws.Name, ws.Name
>> End If
>> Next ws
>> End Sub
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "Stefi" <(E-Mail Removed)> wrote in message
>> news:A1916D63-1FCB-4032-84E5-(E-Mail Removed)...
>> > Hi All,
>> >
>> > I'm trying to create a new collection containing all worksheets in the
>> > active workbook except the last one, but I failed. Please help!
>> >
>> > Regards,
>> > Stefi
>> >
>>
>>
>>

Many thanks, Tom, this is the very method I was looking for.
Stefi


„Tom Ogilvyâ€� ezt Ã*rta:

> Dim wsColl As worksheets
> Set wsColl = ActiveWorkbook.Worksheets
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Stefi" <(E-Mail Removed)> wrote in message
> news:31DAAA87-C940-4F37-831B-(E-Mail Removed)...
> > Thanks Bob! Another question: Isn't it possible to set a new collection to
> > an
> > existing one, I mean something like this:
> >
> > Dim wsColl As Collection
> > Set wsColl = ActiveWorkbook.Worksheets
> >
> > Regards,
> > Stefi
> >
> > "Bob Phillips" ezt Ã*rta:
> >
> >> Sub NewCollection()
> >> Dim wsColl As Collection
> >> Dim ws As Worksheet
> >>
> >> Set wsColl = New Collection
> >> For Each ws In ActiveWorkbook.Worksheets
> >> If ws.Index <> ActiveWorkbook.Worksheets.Count Then
> >> wsColl.Add ws.Name, ws.Name
> >> End If
> >> Next ws
> >> End Sub
> >>
> >> --
> >> ---
> >> HTH
> >>
> >> Bob
> >>
> >> (change the xxxx to gmail if mailing direct)
> >>
> >>
> >> "Stefi" <(E-Mail Removed)> wrote in message
> >> news:A1916D63-1FCB-4032-84E5-(E-Mail Removed)...
> >> > Hi All,
> >> >
> >> > I'm trying to create a new collection containing all worksheets in the
> >> > active workbook except the last one, but I failed. Please help!
> >> >
> >> > Regards,
> >> > Stefi
> >> >
> >>
> >>
> >>
>
>
>