PDA

View Full Version : Solved: Code breaks in iteration of loop.



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!

GTO
10-08-2008, 05:37 PM
Greetings holshy,

I see that this is your first post. Though only recently joining myself, have read site for a couple of years and I believe you will thoroughly enjoy the forum, as there are great folks here who go out of their way to be helpful. I'd also compliment the way you clearly stated the problem as well as the system you're running :-)

Anyways... Maybe I didn't duplicate the required cells/range names properly, but I get an error in the second iteration. From what I see (course I may be not seeing so well), you attempt to rename the second new sheet to the same name as the first one. IE - I don't see where the range "SheetName" is updated, thus the error.

Could you maybe attach the workbook? Remember to take out any sensitive info, including any of yours under properties.

Hope to help,

Mark


Sub CopyCFSheets()

'...statements...
'rename worksheet
.Name = shtExportSheet.Range("SheetName").Value
'...statements...

End Sub

holshy
10-08-2008, 07:53 PM
From what I see (course I may be not seeing so well), you attempt to rename the second new sheet to the same name as the first one. IE - I don't see where the range "SheetName" is updated, thus the error.

Could you maybe attach the workbook? Remember to take out any sensitive info, including any of yours under properties.

That range name points to a cell with a formula in it that keys off the index, so each time iCounter is pushed to the range "SheetIndex" the value of "SheetName" changes. The sheet names are unique so this won't cause a problem.

I cannot attach the workbook. I'm appending the code to a preexisting workbook, a proprietary model.

PS Thanks for the warm welcome. I would hope that I can actually help some people, but I'm sure there are people around who will far outshine my abilities.

GTO
10-08-2008, 08:53 PM
Okay - since I already had to create something to test it in, let's use the attached. With your code unaltered, it seems to run fine to me.

Can you see something different in the attached; or, would this indicate the error is being raised from something else that's happening in conjuction with the code? (Hope that made sense)

Mark

holshy
10-08-2008, 09:34 PM
I'm afraid it might be the complexity of the model that is causing problems. Access has a compare and compact feature. Is there anything like that for XL?

GTO
10-08-2008, 09:57 PM
Reference the 'compact/repair', I do not believe so, but there are many here more knowledgeable than yours truly.

A couple of quick thoughts:

Have you stepped thru the sub from start to finish (or error)?

And have you tried disabling events at the start of the sub and re-enabling them at the end?

Also - presuming you checked the attached and I had the range references similar... I did notice that you copied/pasted vals on the sheet after copying the sheet from the parent wb to the child. I forgot to mention that I'd add in setting copy/paste mode to false and selecting one cell (so the whole sheet's not selected) before moving on to the next sheet.

Mark

david000
10-09-2008, 12:35 AM
See if this helps, if not, back to the drawing board! :think:

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
Dim NewCopy As Worksheet 'added new worksheet object

Application.ScreenUpdating = False 'set application behaviors for speed
Set shtCFSheet = ThisWorkbook.Sheets("CF") 'assign names to the critical sheets in this workbook
Set shtExportSheet = ThisWorkbook.Sheets("Export")

sCopyPath = ThisWorkbook.Path 'get new workbook info
sCopyName = shtExportSheet.Range("OutputWorkbookName")

Set wbCopy = Workbooks.Add 'create new workbooks

wbCopy.SaveAs Filename:=sCopyPath & "\" & sCopyName
iLowIndex = shtExportSheet.Range("StartSheet").Value 'find loop constraints
iHighIndex = shtExportSheet.Range("StopSheet").Value

For iCounter = iLowIndex To iHighIndex
Application.StatusBar = Format((iCounter - iLowIndex) / (iHighIndex - iLowIndex), "0.0%")
DoEvents
shtExportSheet.Range("SheetIndex").Value = iCounter 'increment the index
Application.Calculate 'calculate the workbook
shtCFSheet.Copy After:=wbCopy.Worksheets(wbCopy.Worksheets.Count) 'make a copy of sheet
Set NewCopy = wbCopy.Worksheets(wbCopy.Worksheets.Count) 'Set the new object I added this line
With NewCopy 'rename the sheet and paste values
.Name = shtExportSheet.Range("SheetName").Value 'rename worksheet
.UsedRange.Value = .UsedRange.Value 'paste values (trying to get rid of the copy mode)
End With
Next iCounter

With Application
.DisplayAlerts = False
wbCopy.Worksheets("sheet1").Delete 'get rid of the blank sheet that the book started with
.StatusBar = False 'restore status bar
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Bob Phillips
10-09-2008, 01:57 AM
I'm afraid it might be the complexity of the model that is causing problems. Access has a compare and compact feature. Is there anything like that for XL?

http://www.appspro.com/Utilities/CodeCleaner.htm

mdmackillop
10-09-2008, 05:54 AM
A minor tweak to GTO's code in post #4.

Mark,
Thanks for the sample.

Holshy,
I wouldn't spend time making up a workbook to run your code, if you don't have the inclination to do so yourself. Do you expect all answerers to make up a separate testing routines?

holshy
10-09-2008, 11:22 AM
Do you expect all answerers to make up a separate testing routines?No. I don't expect anybody to try to duplicate my workbook, because I don't expect people to try to answer a question about an error they've never seen before. If they have seen it before, they might already know how to fix it. If they haven't, then they can say, "Wow dude, sucks to be you." and click on to the next thread. Not a big deal.

mdmackillop
10-09-2008, 01:04 PM
Fair enough.
(added to "ignore user" list)

holshy
10-09-2008, 01:22 PM
Fair enough.
(added to "ignore user" list)Wow. Well he apparently won't be able to read this, but if somebody could explain to me what was squelch-worthy about what I said, I'd love to hear it.
<-- 100% Serious.

On another note, I found a work around. How do I mark this resolved? NVMD, Chrome doesn't render the drop downs properly.

david000
10-09-2008, 02:54 PM
Wow. Well he apparently won't be able to read this, but if somebody could explain to me what was squelch-worthy about what I said, I'd love to hear it.
<-- 100% Serious.
IMO – your basic assumption is incorrect.

1)I do try to solve questions I’ve never seen before; just like someone would do a crossword puzzle they’ve never done before.


2)I do not say, "Wow dude, sucks to be you”. If we are a community of likeminded people trying to improve our skills. It’s better to say, "Wow dude, I hope you find the solution to your problem" or (GTO said,” I believe you will thoroughly enjoy the forum, as there are great folks here who go out of their way”). Didn’t that make you feel better than saying, “it sucks to be you”?

holshy
10-09-2008, 03:52 PM
I do not say, "Wow dude, sucks to be you”. If we are a community of likeminded people trying to improve our skills. It’s better to say, "Wow dude, I hope you find the solution to your problem"I see your point...

I don't expect that anybody would actually say that to me; I'm sure you're all a lot nicer than that.

What I was trying to convey is that I don't expect people to go out of their way or to try to recreate the problem in order to help me. I've been on the answering side of these kinds of things enough to know that there's a limit to how much help you can be without seeing what they see, so if I haven't given people enough info, that's my problem, not theirs.

Does that make sense?