Macro to automatically save an Excel with a specific name depending on cell value

[Stefan1979]

Hello,

So far, I created a macro to save a Excel workbook with a specific name depending on the value entered in defined cells:

ActiveWorkbook.SaveAs _
Filename:=Application.GetSaveAsFilename(Range("B4") & " " & Range("B9") & " Form", _
FileFilter:="Excel File(*.xls),"), _
FileFormat:=xlNormal


Until here the macro works fine and correct.

However, I want to extend the macro that the file now gets automatically saved in a specific folder.

The path of the folder is as follow:
C:\Users\Stefan.Ahrendt\Desktop\DART\Client Approval\test.xls

Whereby, "test.xls" is the name of the file that I want to replace by the macro stated above.

Any advise how this can be done?

Thanks,

[HaHoBe]

Hi, Stefan1979,

please use code-tags when displaying code here on Excel Forum.

Use ChDrive and ChDir to get to that directory prior to calling the routine or use InStrRev to get the part of th ename after the last / and add that to the desired path to save.

Please Login or Register  to view this content.

Ciao,
Holger

[Stefan1979]

Hello Holgar,

Thanks for your reply. Updating the code as suggested worked.

One additional question: after running the updated macro, the "Save As" pop-up window still shows up where I have to hit the save button.

Is there a way to modify the macro so that the file gets automatically saved by just running the macro.

Thanks,

[HaHoBe]

Hi, Stefan1979,

could be done with a slight modification:

Please Login or Register  to view this content.

Ciao,
Holger

[Stefan1979]

Hello Holgar,

Thanks for the feedback - works perfect now.