Macro to save as

[Neil Holden]

Hi below is the code I use to create a macro for when pressed the user is
asked to save as.

Is they anyway I can call the file a certain cell name for example what ever
is in A7 and also state the date instead of the user typing the file name.

Also I would like to default the location to a specific place.

Dim flToSave As Variant 'brings up save as dialogue filling in file name
with Job number
Dim flName As String
Dim flFormat As Long

flFormat = ActiveWorkbook.FileFormat
flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel
Files (*.xls),*.xls", _
Title:="Save File As...")

If flToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

 

[Jacob Skaria]

Somthing like the below

Dim strFolder As String
Dim strFile As String

strFolder = "c:\"
strFile = Worksheets("Sheet1").Range("A1")

ThisWorkbook.SaveAs Filename:=strFolder & strFile, _
FileFormat:=ActiveWorkbook.FileFormat

If this post helps click Yes

 

[Dave Peterson]

You're mixing ThisWorkbook and Activeworkbook. I'd be more careful with those.
The activeworkbook doesn't need to be ThisWorkbook.

Option Explicit
Sub testme01()

Dim flToSave As String
Dim flFormat As Long

With ActiveWorkbook 'or thisworkbook
flFormat = .FileFormat

'include a path, date, extension????
flToSave = .Path & "\" & .Worksheets("somesheet").Range("A7").Value _
& "_" & Format(Date, "yyyymmdd") & ".xls"

'no prompt for overwriting an existing file???
Application.DisplayAlerts = False
On Error Resume Next 'just in case the name isn't valid
.SaveAs Filename:=flToSave, FileFormat:=flFormat
If Err.Number <> 0 Then
MsgBox "not saved" & vbLf & Err.Number & vbLf & Err.Description
Err.Clear
End If
On Error GoTo 0
Application.DisplayAlerts = True

End With

End Sub