Save as pdf

[dpaton05]

I have code to save as a pdf but it defaults to the directory that the spreadsheet is located in. I want it to ask where it wants to save the file. Could someone help me with updating this vba code please?

Sub save_pdf()

    Dim currentSheet As Worksheet, fname As String
    fname = ActiveSheet.Range("G7").Value & ActiveSheet.Range("H5").Value    '" for " & ActiveSheet.Range("H5").Value
    
    With ThisWorkbook
        Set currentSheet = .ActiveSheet
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=ThisWorkbook.Path & "\" & fname & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End With
End Sub

 

[Logit]

.
Here is one method :

Option Explicit


Sub SaveOutput()
Dim folderChoice
Dim i As Integer
folderChoice = setupFolders()
ChDir (folderChoice)
    ActiveSheet.Select
     
    Call exportSheet(ActiveSheet.Range("G7").Value & ActiveSheet.Range("H5").Value)
End Sub

Function setupFolders()
Dim FolderName As String
    MsgBox ("Navigate to output directory...")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = 0 Then
            End
        End If
        FolderName = .SelectedItems(1)
    End With
    setupFolders = FolderName
End Function

Sub exportSheet(outputName)
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=outputName & ".pdf"
End Sub

 

[Michael M]

UNTESTED....

Sub MM1()
Dim FileAndLocation As Variant, strFilename As String
strFilename = Range("G7").Value & Range("H5").Value
FileAndLocation = Application.GetSaveAsFilename(InitialFileName:=strFilename, filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
MsgBox "File saved to " & FileAndLocation
End Sub

 

[dpaton05]

Thanks guys, got it to work

 

[dpaton05]

I want a dash between both cells. I thought I could use this code but it wouldn't work. What did I do wrong?

strFilename = Range("G7").Value & " - " Range("H5").Value

 

[dpaton05]

I think I just found it, I missed out an &.

 

[Logit]

.
Glad you have a solution and that I could assist.

Cheers.

 

[dpaton05]

Just tried your solution again Michael and I realised that it doesn't save to pdf but I used the solution from Logit and that worked, thank you.