[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

creating a new collection

Stefi

12/14/2006 11:21:00 AM

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
4 Answers

Bob Phillips

12/14/2006 11:28:00 AM

0

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" <Stefi@discussions.microsoft.com> wrote in message
news:A1916D63-1FCB-4032-84E5-484F678EEB28@microsoft.com...
> 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
>


Stefi

12/14/2006 12:38:00 PM

0

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" <Stefi@discussions.microsoft.com> wrote in message
> news:A1916D63-1FCB-4032-84E5-484F678EEB28@microsoft.com...
> > 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
> >
>
>
>

Tom Ogilvy

12/15/2006 1:25:00 AM

0

Dim wsColl As worksheets
Set wsColl = ActiveWorkbook.Worksheets

--
Regards,
Tom Ogilvy


"Stefi" <Stefi@discussions.microsoft.com> wrote in message
news:31DAAA87-C940-4F37-831B-9C64367EB224@microsoft.com...
> 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" <Stefi@discussions.microsoft.com> wrote in message
>> news:A1916D63-1FCB-4032-84E5-484F678EEB28@microsoft.com...
>> > 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
>> >
>>
>>
>>


Stefi

12/15/2006 7:30:00 AM

0

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" <Stefi@discussions.microsoft.com> wrote in message
> news:31DAAA87-C940-4F37-831B-9C64367EB224@microsoft.com...
> > 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" <Stefi@discussions.microsoft.com> wrote in message
> >> news:A1916D63-1FCB-4032-84E5-484F678EEB28@microsoft.com...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>