VB File Copy or Save Loop

terryhill

New Member
Joined
Jan 23, 2005
Messages
4
Hi,

I am trying make 11 copies of an Excel workbook, AE1.xls, into a directory. At the end, I'd like to have AE1.xls through AE12.xls. All are exact duplicates of the AE1.xls file in the folder.

Is there a simple way to do this?

This is the VB I've tried so far (resused code from Microsoft site - I admit it):

Application.DisplayAlerts = False
filename = Dir(*.xls)
Do While Len (filenam) > 0
newnam = Left (filename, InStr(1,filenam, ".") -1) & ".xls"
ActiveWorkbook.SaveAs Filename:=newnam, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyReconnmended:=False, CreateBackup=False
ActiveWorkbook.Close
Else
MsgBox "Not Able to Open File: " & CurDir () & "\" & filenam
Err=0
End If
Filenam = Dir ( )
Loop
End Sub

This is not really working. I'm not a good programmer. Anyone out there that can help, your assistance is greatly appreciated. I'm going to have to do this about 40 times in 40 directories. Doing it by copying the file and renaming it is not an option.

Thanks,

Terry
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Could you use something like this?:
Code:
Sub test()
Dim i As Integer

Workbooks.Open "AE1.XLS"
For i = 2 To 12
    ActiveWorkbook.SaveAs "AE" & i & ".XLS"
Next i

End Sub

You can hardcode the path, or use a msgbox for it, if you want. Hope that helps!
 

terryhill

New Member
Joined
Jan 23, 2005
Messages
4
Cheers Tazguy37!

That worked great. Much simpler code than what I was trying to modify.

Terry

javascript:emoticon(':coffee:')
Outtahere
 

Forum statistics

Threads
1,147,846
Messages
5,743,515
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top