creating a new collection
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 |
creating a new collection
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 |
creating a new collection
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 |
creating a new collection
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 |
creating a new collection
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 |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com