Print to PDF as name of each sheet.

[derek.hughes]

I have a spreadsheet exported from Access that contains each department's budget information (used, remaining, etc.).

I want to create a command button that when clicked will create a separate PDF for each worksheet. I want each .PDF file to be named the same as the Worksheet name.

Can you help?

Derek

 

[Warship]

Sub SheetsToPDF()
    Dim DestinPath As String
    Dim sh As Worksheet
    
    DestinPath = "C:\XLtest\"
    
    Rem This overwrites any existing PDF w/ no warning
    
    For Each sh In ActiveWorkbook.Worksheets
        If WorksheetFunction.CountA(sh.Cells) > 0 Then
            sh.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=DestinPath & sh.Name, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        End If
    Next sh
End Sub

 

[derek.hughes]

Thank you, Warship. I set up a button, pasted the code in as a macro, changed the destination, and it says "publishing" but it does not appear. What am I doing incorrectly?

Could you give me steps as to where to paste?

Thank you,

Derek

 

[Warship]

if it says "publishing" it is working.
go to the destination fold with Windows and you'll see it there.

if you want it to open right after publishing change this line to =True

OpenAfterPublish:=False

Blank sheets are skipped