Last Updated on January 22, 2022 – 2 min read
How can you save sheets or workbooks as PDF using a VBA macro?
To create a report or a presentation based on Excel data the PDF format can be more readable for your users.
You will discover in this article, how to export sheets, workbooks, range of cells, or Excel charts into a PDF.
Export a Sheet as PDF in VBA
You can save the document in PDF using the ExportAsFixedFormat VBA method. As an argument, you have to specify xlTypePDF for the parameter type.
Sub SaveSheetPDF()
'Save one sheet as PDF
ThisWorkbook.Sheets("Weather").Cells.ExportAsFixedFormat _
Type:=xlTypePDF, FileName:="Desktop", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Export Sheets as PDF in VBA
To export multiple sheets to PDF, you can create a sheet table and use the ExportAsFixedFormat method as previously.
Sub SaveSeveralSheetsPDF()
Sheets("Maps").Range("A1:I49").Select 'Select the range
Sheets("Weather").Activate 'Active the other sheet
ActiveSheet.Range("A1:I49").Select ' Select the range
Sheets(Array("Maps", "Weather")).ExportAsFixedFormat _
Type:=xlTypePDF, FileName:="Desktop", _
Quality:=xlQualityStandard,IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Export all Sheets separately as PDF in VBA
Below is a procedure with a For Each loop to export each sheet to a PDF.
Sub SaveSheetsPDF()
Dim Sh As Sheet
For each Sh in ActiveWorbook.Sheets
Sh.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=Sh.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Next Sh
End Sub
Export a Workbook as PDF in VBA
You can also use the ExportAsFixedFormat method directly on a workbook.
Sub SaveWbkPDF()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="Desktop", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Export a Range as PDF in VBA
You can also export to PDF a well-defined range of cells.
Sub SaveWbkPDF()
ActiveSheet.Range("A1:C40").ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="Desktop", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Export a Chart as PDF in VBA
You can export only a chart object by using the ExportAsFixedFormat method.
Sub SaveChartPDF()
ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="Desktop", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
SKILLS 