CODEWITHSUNDEEP
excelvba
JustME
JustME

Reputation: 33

Create a pdf for each selected Excel sheet

I'm trying to create separate PDFs for each sheet in a selection of sheets, with a name determined by the sheet name and the contents of one cell.

The code is as follows:

Sub SaveWorksheetsAsPDFs()
    Dim sFile       As String
    Dim sPath       As String
    Dim sh          As Object
    Dim InvDate     As String

    With ActiveWorkbook

        sPath = .Path & "\"
        For Each sh In ActiveWindow.SelectedSheets

            InvDate = Format(Range("G9"), "dd-mm-yy")
            sFile = sh.Name & " - Invoice - " & InvDate & ".pdf"
            sh.ExportAsFixedFormat Type:=xlTypePDF, _
                                   Filename:=sPath & sFile, _
                                   Quality:=xlQualityStandard, _
                                   IncludeDocProperties:=False, _
                                   IgnorePrintAreas:=False, _
                                   OpenAfterPublish:=False
        Next sh
    End With
End Sub

Separate PDFs are created and named correctly, however, the PDF contains all the selected sheets instead of one.

Upvotes: 3

Views: 67

Answers (1)

T. Nesset
T. Nesset

Reputation: 417

With my experience, i've seen that the ExportAsFixedFormat always exports all selected sheets.

You can come around this by saying sh.Select after your For Each, and then it should works as you describe in the OP.

Edited code:

Sub SaveWorksheetsAsPDFs()
    Dim sFile       As String
    Dim sPath       As String
    Dim sh          As Object
    Dim InvDate     As String


    With ActiveWorkbook

        sPath = .Path & "\"
        For Each sh In ActiveWindow.SelectedSheets
            sh.Select '<----- New LINE

            InvDate = Format(Range("G9"), "dd-mm-yy")
                sFile = sh.Name & " - Invoice - " & InvDate & ".pdf"
                sh.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=True, _
                                        OpenAfterPublish:=False
        Next sh
    End With
End Sub

Hope this helps you achive your goal. :)

Upvotes: 2

Related Questions