MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro for "Save As"


Posted by Travis on September 25, 2001 7:35 PM

Macros seem to stop recording once you leave the application or go to "Save As". I'm sure there's a way to make a macro that saves a workbook and names it based on a value within the sheet but my VB Skills are lacking.
For example if cell A1 is "Test" then the file would be saved as C:\My Documents\Test.xls
What if A1 is a formula, can the value of the formula become the filename?
Please Help...Thanks


Posted by RobJackson on September 25, 2001 11:05 PM

I haven't tested this but it should work.

Put this in your macro.

Dim PathNm as string
Dim FileNm as string

PathNm="C:\Documents\"
FileNm=Range("A1").Value & ".xls"

If FileNm <> ".xls" then
ThisWorkbook.saveas PathNm & FileNm
else
Display=msgbox("No File Name In Cell A1.",16,"Failure")

It checks to see if cell was blank then if not saves as filename, if so pops up a msgbox with a failure notice. The only thing you might want to do is specify the Range("A1") more accurately. This will pick up the contents of A1 on the sheet you have selected. If this is not necessarily the sheet then you might want to specify a sheet name or reference. ie: Sheets(1).Cells(1,1).value

Good Luck...

R.

Posted by dank on September 25, 2001 11:06 PM

Macros seem to stop recording once you leave the application or go to "Save As". I'm sure there's a way to make a macro that saves a workbook and names it based on a value within the sheet but my VB Skills are lacking.
For example if cell A1 is "Test" then the file would be saved as C:\My Documents\Test.xls
What if A1 is a formula, can the value of the formula become the filename?
Please Help...Thanks

Hello,

This macro will do something like you're after:-

Sub SaveAsCellValue()
Dim rngeFileName As Range, sPath As String

'Change the value A1 to whatever suits your needs
Set rngeFileName = Range("A1")

sPath = "C:\My Documents\"


ActiveWorkbook.SaveAs sPath & rngeFileName.Value

End Sub

Hope it helps,
Dan.