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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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