Looping to create new workbooks using a set “transaction” number

255 pts.
Tags:
Excel VBA
Excel workbooks
LOOP statement
Microsoft Excel 2003
Sequence number generation
VBA
Visual Basic for Applications
I'm playing around a bit and can't figure out why the following code does not save a total of 5 files in My Documents(default) The files should be named in a sequence of 1 - 5 (In my Excel spreadsheet (A1)=1 and (A4)=5) The code works once through, but when ".SaveAs Filename:=Start" comes back around the second time Excel asks if I want to overwrite file "1.xls" b/c it already exists. Shouldn't the below coding allow Excel to save the next file as "2.xls", then "3.xls", and so on? Sub NewTest() Start = Workbooks("Loop Test.xls").Worksheets("Sheet1").Range("A1").Value Finish = Workbooks("Loop Test.xls").Worksheets("Sheet1").Range("A4").Value Do Until Start = Finish Set NewBook = Workbooks.Add With NewBook .SaveAs Filename:=Start Workbooks(Start & ".xls").Close SaveChanges:=True End With Workbooks("Loop Test.xls").Worksheets("Sheet1").Range("A1").Value = Start + 1 Workbooks("Loop Test.xls").Save Loop End Sub Interested to hear an answer. Thanks
ASKED: March 6, 2009  10:49 PM
UPDATED: August 12, 2009  9:49 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your code has a couple of problems (common errors, by the way). (1) Your variable, Start, is never changed within the loop – never changed at all, really. You do change the value of cell A1 to 2, but then you don’t refresh the value of Start by reading A1. (2) You test for the end value, 5, and if true, jump out of the loop before saving worksheet 5.xls. I changed the = to > in the Do-Loop test line to fix that problem.

Here’s a simplified version of the code:

Sub saveFive()
Start = Workbooks(“Loop Test.xls”).Worksheets(“Sheet1″).Range(“A1″).Value
Finish = Workbooks(“Loop Test.xls”).Worksheets(“Sheet1″).Range(“A4″).Value
Do Until Start > Finish
Set NewBook = Workbooks.Add
With NewBook
.SaveAs Filename:=Start
.Close SaveChanges:=True
End With
Start = Start + 1
Loop
End Sub

I left out updating cell A1, but you could put it back in if needed. You also had a line with the Workbooks.Save() method, which is unnecessary because Workbooks.SaveAs has already accomplished that task.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • McNitro
    Never said thanks! This was a BIG help!!! Fixed me right up to move on to BIGGER and "BETTER" problems. LOL! Thanks again!!!
    255 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following