VBA to create a pdf, name it from a cell value and save to the users choice of location

[jdhfch]

Hi there,

I am trying to create a VBA project that does the following:

Selects 2 named sheets - Sheet 1 & Sheet 2
Opens a Save As box for the user to select where they want to save the file
Creates a pdf file of the 2 selected sheets named as the same content in cell G3 and the current date,

I am sure this is easy to do, but it is blowing my mind! Any help would be appreciated,

 

[mumps]

Try:

Sub CreatePDF()
    Application.ScreenUpdating = False
    Dim spath As String
    Sheets(Array("Sheet1", "Sheet2")).Copy
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            spath = .SelectedItems(1)
        End If
    End With
    ChDir spath
    With ActiveWorkbook
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Range("G3") & Date _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

 

[jdhfch]

Try:

Sub CreatePDF()
    Application.ScreenUpdating = False
    Dim spath As String
    Sheets(Array("Sheet1", "Sheet2")).Copy
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            spath = .SelectedItems(1)
        End If
    End With
    ChDir spath
    With ActiveWorkbook
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Range("G3") & Date _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

Thank you, however I get a Runtime Error "1004" Application defined or object defined error?

 

[mumps]

I tested the macro on some dummy sheets and it worked properly. Which line of code is highlighted when you click "Debug"?

 

[jdhfch]

Thanks.. I have fixed the error. However, it closes the excel file after creating the pdf. Is there any way of stopping this, please?

 

[mumps]

Delete this line of code:

.Close False

 

[jdhfch]

Thank you

 

[mumps]

You are very welcome.