holshy
10-08-2008, 04:50 PM
I'm running the following code, which is designed to copy several versions of the same sheet of Thisworkbook into another workbook. On the 25th iteration of the loop, my code errors every time at the bold line.
Excel 2003 (SP3)
Windows XP Professional Edition (SP2), Windows XP Tablet Edition (SP3)
Sub CopyCFSheets()
Dim sCopyPath As String
Dim sCopyName As String
Dim wbCopy As Workbook
Dim iCounter As Integer
Dim iLowIndex As Integer
Dim iHighIndex As Integer
Dim shtCopiedCF As Worksheet
Dim shtCFSheet As Worksheet
Dim shtExportSheet As Worksheet
Dim bFirstSheet As Boolean
'set application behaviors for speed
Application.ScreenUpdating = False
'assign names to the critical sheets in this workbook
Set shtCFSheet = ThisWorkbook.Sheets("CF")
Set shtExportSheet = ThisWorkbook.Sheets("Export")
'get new workbook info
sCopyPath = ThisWorkbook.Path
sCopyName = shtExportSheet.Range("OutputWorkbookName")
'create new workbooks
Set wbCopy = Workbooks.Add
wbCopy.SaveAs Filename:=sCopyPath & "\" & sCopyName
'find loop constraints
iLowIndex = shtExportSheet.Range("StartSheet").Value
iHighIndex = shtExportSheet.Range("StopSheet").Value
For iCounter = iLowIndex To iHighIndex
'user friendly is good
Application.StatusBar = Format((iCounter - iLowIndex) / (iHighIndex - iLowIndex), "0.0%")
DoEvents
'increment the index
shtExportSheet.Range("SheetIndex").Value = iCounter
'calculate the workbook
Application.Calculate
'make a copy of sheet
shtCFSheet.Copy After:=wbCopy.Worksheets(wbCopy.Worksheets.Count)
'rename the sheet and paste values
With wbCopy.Worksheets("CF")
'rename worksheet
.Name = shtExportSheet.Range("SheetName").Value
'paste values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
End With
Next iCounter
'get rid of the blank sheet that the book started with
Application.displayalerts = False
wbCopy.Worksheets("sheet1").Delete
'restore status bar
Application.StatusBar = False
End Sub
When I run the code right now the raised is runtime error 1004 and the text is "Method 'Copy' of object '_Worksheet' failed".
I tried declaring shtCFSheet as a worksheet object and setting shtCFSheet = Thisworkbook.Sheets("CF"). In that case the error raised is still runtime error 1004, but the text is "Copy method of Worksheet class failed".
I also tried giving the "CF" worksheet a codename of "CFSheet". I don't recall what the error was, but the loop still broke on the same line at the 25th iteration.
BREAK MODE FINDINGS
If I try to step into the line the same error is raised.
"?wbCopy.Worksheets.Count" will resolve in the immediate window. The value is 25 (not surprising).
The command "shtCFSheet.Copy" can be run in the immediate window. If I then enter run mode again, the line will execute and the code continues until iteration 30, where it produces the same error at the same point.
This is the part where I start using terms I know just enough about to be dangerous. Could this be a memory leak? If so, is there a way to force garbage collection on each iteration to prevent it? If not, are there any other suggestions.
Thanks for the help!
Excel 2003 (SP3)
Windows XP Professional Edition (SP2), Windows XP Tablet Edition (SP3)
Sub CopyCFSheets()
Dim sCopyPath As String
Dim sCopyName As String
Dim wbCopy As Workbook
Dim iCounter As Integer
Dim iLowIndex As Integer
Dim iHighIndex As Integer
Dim shtCopiedCF As Worksheet
Dim shtCFSheet As Worksheet
Dim shtExportSheet As Worksheet
Dim bFirstSheet As Boolean
'set application behaviors for speed
Application.ScreenUpdating = False
'assign names to the critical sheets in this workbook
Set shtCFSheet = ThisWorkbook.Sheets("CF")
Set shtExportSheet = ThisWorkbook.Sheets("Export")
'get new workbook info
sCopyPath = ThisWorkbook.Path
sCopyName = shtExportSheet.Range("OutputWorkbookName")
'create new workbooks
Set wbCopy = Workbooks.Add
wbCopy.SaveAs Filename:=sCopyPath & "\" & sCopyName
'find loop constraints
iLowIndex = shtExportSheet.Range("StartSheet").Value
iHighIndex = shtExportSheet.Range("StopSheet").Value
For iCounter = iLowIndex To iHighIndex
'user friendly is good
Application.StatusBar = Format((iCounter - iLowIndex) / (iHighIndex - iLowIndex), "0.0%")
DoEvents
'increment the index
shtExportSheet.Range("SheetIndex").Value = iCounter
'calculate the workbook
Application.Calculate
'make a copy of sheet
shtCFSheet.Copy After:=wbCopy.Worksheets(wbCopy.Worksheets.Count)
'rename the sheet and paste values
With wbCopy.Worksheets("CF")
'rename worksheet
.Name = shtExportSheet.Range("SheetName").Value
'paste values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
End With
Next iCounter
'get rid of the blank sheet that the book started with
Application.displayalerts = False
wbCopy.Worksheets("sheet1").Delete
'restore status bar
Application.StatusBar = False
End Sub
When I run the code right now the raised is runtime error 1004 and the text is "Method 'Copy' of object '_Worksheet' failed".
I tried declaring shtCFSheet as a worksheet object and setting shtCFSheet = Thisworkbook.Sheets("CF"). In that case the error raised is still runtime error 1004, but the text is "Copy method of Worksheet class failed".
I also tried giving the "CF" worksheet a codename of "CFSheet". I don't recall what the error was, but the loop still broke on the same line at the 25th iteration.
BREAK MODE FINDINGS
If I try to step into the line the same error is raised.
"?wbCopy.Worksheets.Count" will resolve in the immediate window. The value is 25 (not surprising).
The command "shtCFSheet.Copy" can be run in the immediate window. If I then enter run mode again, the line will execute and the code continues until iteration 30, where it produces the same error at the same point.
This is the part where I start using terms I know just enough about to be dangerous. Could this be a memory leak? If so, is there a way to force garbage collection on each iteration to prevent it? If not, are there any other suggestions.
Thanks for the help!