Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I want to treat all the sheets in a workbook, except for the first one, the
same way in various methods. So I wanted do something like: Public NonControlWorksheetCollection As New Collection Private Sub createNonControlWorksheetCollection() ' populate a collection of Sheets that doesn't include the Control Worksheet ' assumes the control worksheet is worksheet 1 Dim counter As Integer Dim wks As Worksheet Debug.Assert ThisWorkbook.Worksheets(1).Name = CONTROL_WKS_NAME For counter = 2 To (ThisWorkbook.Worksheets.Count) Set wks = ThisWorkbook.Worksheets(counter) NonControlWorksheetCollection.Add (wks) <--------------------error: method or property not supported Next counter End Sub Since this didn't work, I did the below two methods, but it seems there should be a more elegant solution. TIA ------------------------------------------------------------- Private Sub createNonControlWorksheetCollection() ' populate a collection of Sheets that doesn't include the Control Worksheet ' assumes the control worksheet is worksheet 1 Dim counter As Integer Debug.Assert ThisWorkbook.Worksheets(1).Name = CONTROL_WKS_NAME For counter = 2 To (ThisWorkbook.Worksheets.Count) NonControlWorksheetCollection.Add (counter) Next counter End Sub Public Function getWorksheet(index As Integer) ' To ease the burden of object creation in methods needing nonControlWorksheets ' returns a Worksheet object for the passed index Dim wks As Worksheet Set wks = ThisWorkbook.Worksheets(index) End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
creating cell references | Excel Discussion (Misc queries) | |||
collection of sheets | Excel Programming | |||
Creating a formula that references other sheets | Excel Worksheet Functions | |||
creating array using name references | Excel Programming | |||
How to find the type of Sheet in Excel.Workbook.sheets collection | Excel Programming |