Macro to save as

N

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
 
J

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top