Need help with my code!
 

I have to duplicate a file about 1500 times with unique names. I am
trying to write a macro that will automatically save the file as
excel(X).xls where (X) is auto generated for as many times as I need.

I am new to basic but what I have cobbled together from different posts
is ...

Dim X As Long
Dim sFilename As String
For X = 1 To 1500
With ActiveWorkbook.(X)
sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls"
ActiveWorkbook.SaveAs
End With
Next

If someone wouldn't mind telling me what have I got wrong?

Thanks for the help,
Steve

Need help with my code!
 

Here's what I came up with. This takes the active open workbook and saves
it 10 times with the same name plus an incremented number into the same
location.

Sub Foo_ManySave()

Dim objWkbk As Workbook
Dim strFPath As String
Dim strFName As String
Dim x As Long

Set objWkbk = ActiveWorkbook
strFPath = "C:\Documents and Settings\UserName\Desktop\New Folder"

For x = 1 To 10
strFName = strFPath & "\NewBook" & x & ".xls"
objWkbk.SaveAs strFName
Set objWkbk = ActiveWorkbook
Next x

End Sub

You will, of course, have to adjust the str variables to suit your own
parameters.
Ed

"smonczka" wrote in message
oups.com...
I have to duplicate a file about 1500 times with unique names. I am
trying to write a macro that will automatically save the file as
excel(X).xls where (X) is auto generated for as many times as I need.

I am new to basic but what I have cobbled together from different posts
is ...

Dim X As Long
Dim sFilename As String
For X = 1 To 1500
With ActiveWorkbook.(X)
sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls"
ActiveWorkbook.SaveAs
End With
Next

If someone wouldn't mind telling me what have I got wrong?

Thanks for the help,
Steve

Need help with my code!
 

smon,

You almost had it write...here is your code with the needed changes. You
needed to remove the .(X) from ActiveWorkbook and add Filename:=sFilename at
the end of the SaveAs line. So your code would look like this:

Dim X As Long
Dim sFilename As String
For X = 1 To 1500
With ActiveWorkbook
sFilename = "c:\temp\excel" & CStr(X) & ".xls"
ActiveWorkbook.SaveAs Filename:=sFilename
End With
Next

"smonczka" wrote:

I have to duplicate a file about 1500 times with unique names. I am
trying to write a macro that will automatically save the file as
excel(X).xls where (X) is auto generated for as many times as I need.

I am new to basic but what I have cobbled together from different posts
is ...

Dim X As Long
Dim sFilename As String
For X = 1 To 1500
With ActiveWorkbook.(X)
sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls"
ActiveWorkbook.SaveAs
End With
Next

If someone wouldn't mind telling me what have I got wrong?

Thanks for the help,
Steve

Need help with my code!
 

Wow you mean I really wasn't that far off? Maybe I am actualy learning
something. :)

Thanks to both of you for your posts, I was afraid I was going to have
to do this maualy.

Thanks again.
Steve