VBA -Save as code

[Alisya]

Hi, i am after a Vba save as code where the code will prompt save as dialogue via named range "rngFile" i.e the below path.


C:\Users\Test\Documents\VBA & Excel

 

[VoG]

Try like this

Sub SaveMe()
Dim fName As Variant
fName = Application.GetSaveAsFilename _
        (InitialFileName:="C:\Users\Test\Documents\VBA\" & Range("rngFile").Value & ".xls", _
        FileFilter:="Excel Files (*.XLS), *.XLS", _
                     Title:="Save As")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
End Sub

 

[Alisya]

Hi Vog,

I was hoping the file path "C:\Users\Test\Documents\VBA\" not to be hard coded as i have the named range "rngFile" on another worksheet, so the user can amend the path as they choose.

Try like this

Sub SaveMe()
Dim fName As Variant
fName = Application.GetSaveAsFilename _
        (InitialFileName:="C:\Users\Test\Documents\VBA\" & Range("rngFile").Value & ".xls", _
        FileFilter:="Excel Files (*.XLS), *.XLS", _
                     Title:="Save As")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
End Sub

 

[VoG]

If rngFile contains the full path and filename try

Sub SaveMe()
Dim fName As Variant
fName = Application.GetSaveAsFilename _
        (InitialFileName:=Range("rngFile").Value, _
        FileFilter:="Excel Files (*.XLS), *.XLS", _
                     Title:="Save As")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
End Sub

 

[Alisya]

Thanks Vog

If rngFile contains the full path and filename try

Sub SaveMe()
Dim fName As Variant
fName = Application.GetSaveAsFilename _
        (InitialFileName:=Range("rngFile").Value, _
        FileFilter:="Excel Files (*.XLS), *.XLS", _
                     Title:="Save As")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
End Sub