Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and collections
Hi,
I'm running a macro in Excel 2003, which I know need to change to only apply to specific sheets in my workbook (2 thru 5 of 7). I tried to build a collection of these sheets and failed. Any ideas on how to apply my macro to specific sheets, either with or without a building collection, greatly received. Regards Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and collections
I use the code below to get every worksheet and then test worksheet name to
determine how I process the sheets. You could name each of the wroksheet the same 1st 4 letters and then use left(wsname,4) = "good" Sub test() For Each ws In Worksheets wsname = ws.Name If wsname = "Sheet1" Then a = 1 End If Next ws End Sub "DanW" wrote: Hi, I'm running a macro in Excel 2003, which I know need to change to only apply to specific sheets in my workbook (2 thru 5 of 7). I tried to build a collection of these sheets and failed. Any ideas on how to apply my macro to specific sheets, either with or without a building collection, greatly received. Regards Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and collections
Hi Dan,
Try something like: '================ Public Sub TesterX() Dim WB As Workbook Dim i As Long Set WB = Workbooks("MyBook.xls") '<<=== CHANGE For i = 2 To 5 'Do something, e.g.: MsgBox WB.Sheets(i).Name Next i End Sub '<<================ --- Regards, Norman "DanW" wrote in message ... Hi, I'm running a macro in Excel 2003, which I know need to change to only apply to specific sheets in my workbook (2 thru 5 of 7). I tried to build a collection of these sheets and failed. Any ideas on how to apply my macro to specific sheets, either with or without a building collection, greatly received. Regards Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and collections
Dan, more than likely you have a macro that works just the way you want it
to do on the active sheet. Let's assume your macro is called "MyMacro" Then Sub ChangeManySheets() Worksheets("Sheet1").Select MyMacro Worksheets("Sheet4").Select MyMacro 'and so so forth End Sub The above selects a sheet and then runs your macro. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "DanW" wrote in message ... Hi, I'm running a macro in Excel 2003, which I know need to change to only apply to specific sheets in my workbook (2 thru 5 of 7). I tried to build a collection of these sheets and failed. Any ideas on how to apply my macro to specific sheets, either with or without a building collection, greatly received. Regards Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and collections
Normally a collection is nore required but sometimes they can be handy. If
you really want collection then here is some sample code... Public colMySheets As Collection Sub PopulateCollection() Dim wks As Worksheet Set colMySheets = New Collection colMySheets.Add Sheet1, Sheet1.CodeName colMySheets.Add Sheet2, Sheet2.CodeName For Each wks In colMySheets MsgBox wks.Name Next wks End Sub -- HTH... Jim Thomlinson "DanW" wrote: Hi, I'm running a macro in Excel 2003, which I know need to change to only apply to specific sheets in my workbook (2 thru 5 of 7). I tried to build a collection of these sheets and failed. Any ideas on how to apply my macro to specific sheets, either with or without a building collection, greatly received. Regards Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collections problem | Excel Programming | |||
Collections of Collections | Excel Programming | |||
Help with collections | Excel Programming | |||
Using Collections | Excel Programming | |||
Collections Lookups | Excel Programming |