File save prompt for location

[josros60]

Hi,

I have tried to modify the code below to select the folder to save at but not luck, how to modify it to prompt for saving location and start at this location:

C:\Users\jose.rossi\Desktop\Excel Files\Reconciliations\

code:

Sub PDFActiveSheet()
'Varriables
Dim SaveRng As Range
Dim pdfname As String
Dim path As String


'Setting range to be saved
Set SaveRng = Range("SAVE_PDF")

'setting file name with a cell value
CurrentMonth = Format(ActiveSheet.Range("E1").Value, "mm-dd-yyyy")
pdfname = Range("V2") & "_" & CurrentMonth

'path
path = "C:\Users\jose.rossi\Desktop\Excel Files\Reconciliations\"

'save the range as pdf
SaveRng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=path & pdfname & ".pdf"


End Sub

Thank you,

 

[Domenic]

Try replacing...

path = "C:\Users\jose.rossi\Desktop\Excel Files\Reconciliations\"

with

    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select"
        .InitialFileName = "C:\Users\jose.rossi\Desktop\Excel Files\Reconciliations\"
        .Title = "Select Folder"
        If .Show = 0 Then Exit Sub
        path = .SelectedItems(1) & "\"
    End With

Actually, I would suggest placing the above code at the beginning of your macro. This way, if the user cancels the file dialog box, the code can exit the sub without executing any other lines of code.

Hope this helps!

 

[josros60]

Thank you so much, worked perfecty.

 

[Domenic]

You're very welcome, cheers!