macros and collections

[Guest]

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

 

[Guest]

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

 

[Norman Jones]

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
'<<================

 

[Bob Flanagan]

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

 

[Guest]

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