McNitro 
190 pts. | Aug 12 2009 9:49PM GMT
Never said thanks!
This was a BIG help!!! Fixed me right up to move on to BIGGER and “BETTER” problems. LOL!
Thanks again!!!
Q:
Looping to create new workbooks using a set "transaction" number
VBA, Excel VBA, LOOP statement, Microsoft Excel 2003, Excel workbooks, Sequence number generation, 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
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:
Mar 6 2009 10:49 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
+1
Click to Vote:
+1
Click to Vote:
- 1
0
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.
