File Names

[Silo]

Hello everyone

It’s been a while since I needed some help from the experts here.

I have a task I would like some assistance with…

I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.

I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?

Any help would be appreciate it

Thank you guys in advance

 

[jmthompson]

Will there be no difference in the resulting file beyond the file name?

 

[erik.van.geit]

Hi,

Have a look in the VBA helpfiles for
SaveCopyAs

kind regards,
Erik

 

[Silo]

Hi jmthompson

Other then the file name there's nothing different

Thanks

 

[jmthompson]

If you were going to create a list of filenames to maintain, you might as well keep it in the macro. Here is code you can modify for your use.

Update myPath to equal the path you want to assign to the resulting files (assuming you want them all saved in the same or similar place)

Update MyF to any portion of the resulting file name that will be common to all resulting files.

If you need help figuring out myPath and MyF, let me know

Sub Save_As()
Application.DisplayAlerts = False
myPath = "C:\Big Folder\Little Folder\tiny folder\"
myF = "newfile"
ActiveWorkbook.SaveCopyAs myPath & myF & "1.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "2.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "3.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "4.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "5.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "6.xls"
ActiveWorkbook.SaveCopyAs myPath & myF & "7.xls"
Application.DisplayAlerts = True
End Sub

 

[Silo]

Thank you, I'll try this and post back

 

[jmthompson]

It might be helpful if I tell you the above code would result in the following files:
C:\Big Folder\Little Folder\tiny folder\newfile1.xls
C:\Big Folder\Little Folder\tiny folder\newfile2.xls
C:\Big Folder\Little Folder\tiny folder\newfile3.xls
C:\Big Folder\Little Folder\tiny folder\newfile4.xls
C:\Big Folder\Little Folder\tiny folder\newfile5.xls
C:\Big Folder\Little Folder\tiny folder\newfile6.xls
C:\Big Folder\Little Folder\tiny folder\newfile7.xls

 

[Silo]

Hello jmthompson

I edited the code and get an error, the error says

Complie error, Syntax error

Then the debuger points to...

ActiveWorkbook.SaveCopyAs myPath & T1 Butler July - Dec 2008 & ".xls"

Can you tell me what I've overlooked?

Thanks

 

[jmthompson]

Try this:

ActiveWorkbook.SaveCopyAs myPath & "T1 Butler July - Dec 2008.xls"

 

[Silo]

Excellent, that worked Thanks jmthompson