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 he
http://support.microsoft.com/default...4&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!