Macro - File Save As

[Guest]

I have 7 identical files which I routinely save to another drive and remove the links. I've recorded a macro that will move the files, but I have to edit it each time to adjust the name of the file. Can the macro be adjusted to move the active file instead of specifying the name of the file? Here's the macro as I currently have it recorded: (This is MPA Attorney, but I would save MPA Agency next, then MPA Internal, etc..

ActiveWorkbook.SaveAs Filename:=
"I:\EXLDATA\Loan Recovery MIS\2004_03\Outstandings\MPA Attorney.xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=Fals
Sheets(Array("All Roll-up", "PEL_Rev Summary", "AF DFS Summary", "ARP",
"Auto Finance", "BCS", "Commercial", "DDA", "DFS", "Direct", "FUHEB", "FUMC", "HEL",
"Lease", "TMS", "TMS-PEL", "W&T", "Revolving", "PEL")).Selec
Sheets("All Roll-up").Activat
Cells.Selec
Selection.Cop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Range("B3").Selec
Sheets("All Roll-up").Selec
End Sub

 

[Dave Peterson]

So you want to save the file into a different folder, but with the same
filename?

If yes:

Dim myFileName As String
myFileName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:= _
"I:\EXLDATA\Loan Recovery MIS\2004_03\Outstandings\" & myFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If you really meant you want to save it in place (overwrite the original), then
you could do your save (not .saveas), then close it.

Then look a Name in VBA's help to move it to that other location.

VBA also has a FileCopy that can copy a file (not an open workbook) to a new
location.

 

[Dave Peterson]

Watch your double quotes.

"I:\EXLDATA\Loan Recovery MIS\2004_03\Outstandings\" & myFileName, _

Notice that the & myfilename isn't within the double quotes in this example.