For Each ... Next loop - need to reference the loop variable

[neonx3]

I have a for-each loop which loops through worksheets. Due to an error
in Excel, and because I am copying a lot of sheets, I need to save,
close, and open the workbook several times from within the for-each
loop to prevent Excel from crashing (I'm basically using the workaround
proposed here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw).
I'm using Excel 2000.

This resets the reference to my worksheet in the for-each loop, and I'm
not sure how to save that information so I can 'reassign' the reference
after re-opening the workbook (within the loop). I tried saving the
name of the worksheet and then using a Set statement on the loop
variable (shtLL) to retrieve that particular worksheet, but that didn't
work. When it got to the "next" statement it acted as though it had
completed the for-each loop.

Here's some demonstrative code:

Dim shtLL As Worksheet
Dim wkbkLL As Workbook
Dim tempName as String

For Each shtLL In wkbkLL.Worksheets
tempName = shtLL.name

wkbkLL.Close savechanges:=True
Set wkbkLL = Nothing
Set wkbkLL = Application.Workbooks.Open(wkbkLLPath)

set shtLL = wkbkLL.Worksheets(tempName)
Next


Thanks for any help you can give!

 

[Guest]

If you have to loop through all of the sheets, maybe save the number of
worksheets to a variable before going into the loop, then use a For/Next
loop. Of course, I don't know the destination of the copies you are making -
this would only work if the destination is at the end of the workbook
(otherwise Worksheets(i) will refer to the wrong sheet).

Anyway, code along these lines this worked fine for me

x = WkBk.Worksheets.Count
For i = 1 to x
WkBk.Worksheets(i).Copy After:= WkBK.Sheets(WkBk.Sheets.Count)
If i mod 10 = 0 then
WkBk.Save
WkBk.Close
Set WkBk = Nothing
Set WkBk = Workbooks.Open (Filename)
End If
Next i

 

[Daniel]

Thanks very much for your assistance. Copying a sheet to the end seems
to take a bit longer, but at least I can get it to work.

Danny

 

[Guest]

I don't know the technical reasons, but I suspect the problem is with the
For/Each loop object variable being destroyed. I saw you tried to
re-establish the link, but I'm skeptical it works like that.

Many times I use For/Each to loop through a range (example below) and use
the same object variable (without destroying it first - probably a bad
practice). But even though rngCell is already assigned to a cell, the second
for loop will still work okay. Therefore, I think VBA must keep its own
counter and not rely on what the existing value is of the object variable
used as a counter. And, once the variable is destroyed (by closing the
workbook), maybe VBA's internal counter is also destroyed?

For Each rngCell in Myrange1
...
Next rngCell

For Each rngCell in MyRange2
.....
Next rngCell