Macro bug - looping thru multiple worksheets - workaround?

[Bruce Erlichman]

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/defaul...port/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

 

[MWE]

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

 

[Tom Ogilvy]

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.

 

[Bruce Erlichman]

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!

 

[Tom Ogilvy]

I assume you are asking MWE since you posted below his partial response.
However, since he hasn't responded.

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

would allow you to list the sheets vertically without incurring a syntax
error. However, there is some limit on the number of line continuation
characters you can have - I don't know what it is.

If you want to loop through all sheets in the workbook
Sub Test1()
For Each x In ActiveWorkbook.Worksheets
x.Activate
ActiveCell.FormulaR1C1 = "=(RC[-4]+RC[-3])"
Range("G2").Select
Next x
End Sub

Of there are only one or two to exclude
Sub Test1()
For Each x In ActiveWorkbook.Worksheets
if x.Name <> "Sheet1" and x.Name <> "Sheet2" then
x.Activate
ActiveCell.FormulaR1C1 = "=(RC[-4]+RC[-3])"
Range("G2").Select
End If
Next x
End Sub