Excel macro to save a spreadsheet in desired folder

[jupops]

Good Evening all,

Could you please help, I am trying to setup a macro in excel that will save-as (using a cell's data for its title)This works as required but it saves to the default folder. I need to save it in another location, either by typing the path into the coding or preferably having the save as window where I can choose the end location.

The coding I have so far is:

Sub SaveAs()
ThisFile = Range("K5").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub


Cheers Jupops

 

[PHV]

I'd use the GetSaveAsFilename method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RomeERome]

Jupops,

You can do something similar to this. This is something that I'm using.

ActiveWorkbook.SaveAs Filename:="Drive Letter:\Path\Filename.ext"

Since you want to use the value of a cell, I would assign that value to a variable, and insert into your macro as follows:

Dim FNVariable As String
FNVariable = Range("K5").Value
ActiveWorkbook.SaveAs Filename:="Drive Letter:\Path\" & FNVariable & ".ext"

If your location is static this is one way, but if your location is dynamic, you can use an input box and assign that to a variable, and add it to your macro. You could also use the GetSaveAsFilename method as PHV suggested.

I hope this works for you. Let me know.