VBA save as PDF based on cell values for path and file name

[M4TVD]

I am trying to save a PDF of the sheet that i am working on (only the sheet selected and not the whole workbook)
the file path is written in cell "M12"
the file name that I want to create for the sheet is "E5" then "E6" then "B6"

I have this code but it is not working, think i have typos somewhere in it:

Public Sub Save_Sheets_As_PDF()

    Dim PDFfile As String
    Dim currentSheet As Worksheet
    
    With ActiveWorkbook
        PDFfile = .ActiveSheet.Range("m12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .ActiveSheet.Range("e5" & "E6" & "b6").Value
        Set currentSheet = .ActiveSheet
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
        MsgBox "Created " & PDFfile
    End With
    
End Sub

 

[M4TVD]

When i try and run this code, i get a runtime error '1004'

 

[M4TVD]

I have also tried this code:

Sub SaveAsPDF()
'Saves active worksheet as pdf using concatenation
'of E5,E6,B6

Dim fName As String
With ActiveSheet
    fName = .Range("E5").Value & .Range("E6").Value & .Range("B6").Value
    .ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Users\'InsertFolderStructureHere'\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

this is not working for me either

 

[John_w]

I am trying to save a PDF of the sheet that i am working on (only the sheet selected and not the whole workbook)
the file path is written in cell "M12"
the file name that I want to create for the sheet is "E5" then "E6" then "B6"

Try this macro:

Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfile As String
    
    With ActiveWorkbook.ActiveSheet
        PDFfile = .Range("M12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value .Range("B6").Value
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        MsgBox "Created " & PDFfile
    End With
    
End Sub

 

[M4TVD]

I have tried your suggested code but I get this message:

 

[KalilMe]

@M4TVD
change this

PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value .Range("B6").Value

to this

PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value & .Range("B6").Value

there is a typo about ampersand

 

[M4TVD]

Thankyou, i changed this, and got the following:

When I Debug:

 

[John_w]

Sorry, I omitted the ampersand.

Does M12 contain a valid path?

Is the concatenation of E5, E6 and B6 a valid file name?

 

[M4TVD]

Yes M12 contains a valid path,

I want the file name to be what is written in cells E5, E6 and B6.

no PDF file exists yet.

 

[John_w]

Is the concatenation of E5, E6 and B6 a valid file name?

This works for me, with test values in the cells. It creates C:\Temp\123.pdf.

Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfile As String
    
    With ActiveWorkbook.ActiveSheet
        .Range("M12").Value = "C:\Temp\"
        .Range("E5").Value = "1"
        .Range("E6").Value = "2"
        .Range("B6").Value = "3"
        
        PDFfile = .Range("M12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value & .Range("B6").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        MsgBox "Created " & PDFfile & ".pdf"
    End With
    
End Sub