VB File Copy or Save Loop

[terryhill]

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

 

[Tazguy37]

Could you use something like this?:

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]

Cheers Tazguy37!

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

Terry

javascript:emoticon('')
Outtahere