For Each, Next

[Guest]

I have the following macro than contains another macro:

Sub MonthList()

Dim wks As Worksheet

For Each wks In Worksheets
OtherMacro
Next wks

End Sub

However, when i run it, OtherMacro (a list of months) only
get executed in the active worksheet. What am i doing
wrong?

regards

 

[Frank Kabel]

Hi
you probably hardcoded the activesheet in your 'othermacro'. You have
to pass the worksheet object to your other macro and use this as
reference for your macro

 

[Bob Phillips]

Not the correct approach IMO, Frank's suggestion is the way to go, but you
could do this

Sub MonthList()

Dim wks As Worksheet
Dim orig as Worksheet

Set orig = Activesheet
For Each wks In Worksheets
wks.Activate
OtherMacro
Next wks
orig.Activate

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)