Macro bug - looping thru multiple worksheets - workaround?
 

Hi folks:

There is a known/admitted bug in creating a macro that
loops through multiple worksheets in a workbook - see:

http://support.microsoft.com/default...NoWebContent=1

The 2 workarounds provided in the above do not work
(at least for me) - the macros still onlu execute on the
first worksheet.

Anyone out there have a solution?

Much thanks, in advance.

Bruce

Macro bug - looping thru multiple worksheets - workaround?
 

I have dozens of applications that loop through worksheets in a give
workbook. However, I use brute force (my FORTRAN background i
showing) because it is simplier to see what is going on. I do thing
like

--
Message posted from http://www.ExcelForum.com

Macro bug - looping thru multiple worksheets - workaround?
 

It would be hard to fathom why the workarounds presented would not work. I
suspect you have omitted some important aspect of the method and therefore
continue to work on the activesheet or for some similar reason related to
your coding, the actions are only taken on one sheet.

By the way, this isn't a known/admitted bug. It is a statement of the way
VBA works in conjunction with Excel. Grouped sheets basically are not
supported in VBA (although some actions have workarounds with application to
multiple sheets).

Anyway, it would be hard to identify you error without seeing your code.

--
Regards,
Tom Ogilvy

Bruce Erlichman wrote in message
et...
Hi folks:

There is a known/admitted bug in creating a macro that
loops through multiple worksheets in a workbook - see:


http://support.microsoft.com/default...microsoft.com:
80/support/kb/articles/q126/3/13.asp&NoWebContent=1

The 2 workarounds provided in the above do not work
(at least for me) - the macros still onlu execute on the
first worksheet.

Anyone out there have a solution?

Much thanks, in advance.

Bruce

Macro bug - looping thru multiple worksheets - workaround?
 

You were right – the MS work-around worked,
as follows:

Sub Test1()
Dim x As Object
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
For Each x In ActiveWindow.SelectedSheets
ActiveCell.FormulaR1C1 = "=(RC[-4]+RC[-3])"
Range("G2").Select
Next x
End Sub

But this didn’t:

Sub Test1()
Dim x As Object
Sheets(Array("Sheet1",
"Sheet2",
"Sheet3")).Select
For Each x In ActiveWindow.SelectedSheets
ActiveCell.FormulaR1C1 = "=(RC[-4]+RC[-3])"
Range("G2").Select
Next x
End Sub


The reason for listing the sheets in a vertical
fashion is because the actual problem involves
several hundred sheets and wrap-around was cumber-
some.

Any way to reference these sheets in a global
manner, without listing each and every one,
like "Sheet1-Sheet3"?


Much thanks for your time.


Bruce Erlichman



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!