Use cell value to "Save as" file Name

[andrzejls]

Hi,
I am not an expert in Excel, so I would like to ask a question to the "gurus". What I would like to do is to use a cell value (for example: Test) to be used as a flie name when I go to "Save as...". Is it possible ?.

Thanks

 

[Clyde38]

I don't know if you can "save as" directly from the drop down menu, however the following macro will do what you want:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim rngPullName As Range
Dim strSavePath As String
Application.DisplayAlerts = False
Set rngPullName = ThisWorkbook.Sheets("Name of the sheet or sheet #").Range("Cell that holds the name you want to use")
strSavePath = ThisWorkbook.Sheets("Name of the sheet or sheet #").Range("Cell that holds the path you want to use ")
Calculate
ActiveWorkbook.SaveAs Filename:=strSavePath & rngPullName & ".xls"
If ThisWorkbook.Saved = True Then
Cancel = True
End If
Application.DisplayAlerts = True

 

[Clyde38]

Sorry, copy paste missed the "End Sub" statement.