Excel Macro to Save as PDF (5 Suitable Examples)

PDF is a very common file format for documents. In Excel 2010 and later, you can export a workbook, a sheet or group of selected worksheets, or a range as a PDF. In this tutorial, you will learn 5 examples of Excel macro to save as pdf.


Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article.


5 Examples of Excel Macro to Save as PDF

This part includes the main codes to save Excel files as PDFs from several things such as workbooks, worksheets, selected regions, and ranges. It is a method of ExportAsFilxedFormat mixed with the type property xlTypePDF that generates a PDF.

1. Macro to Save Active Workbook as PDF

To save an entire workbook as a pdf with a VBA code, you need to look out the following steps.

Steps:

  • First, you need to select all sheets that you wish to save as pdf. You can easily select all the sheets by right-clicking on any sheet name and then choosing the Select All Sheets option from the list.
  • Now, you see all the sheets are selected. Right-click on any sheet name, and click on View Code from the list. A module window will pop up.

Saving Active Workbook as PDF

  • Copy the following macro and paste it into the module window. After that, modify the saveLocation part of the code with your own pdf file save location. Now, click on the Run button.
Sub SavingActiveWorkbookAsPDF()
Dim saveLocation As String
saveLocation = "C:\Users\sohan\Desktop\Softeko\57-0040\PDF files\mypdf2.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

Macro to Save Active Workbook as PDF

  • Now, to see the pdf, go to the file location that you put into the VBA code for saving exported pdf from Excel.

Saving Active Workbook as PDF


2. Macro to Save Active Worksheet as PDF

To save the active worksheet as a pdf with a macro, just follow the steps below.

Steps:

  • First, go to the sheet that you need to save as a pdf.

Saving Active Worksheet as PDF

  • Then, click on Developer >> Visual Basic >> Insert >> Module. A module window will pop up.

Saving Active Worksheet as PDF

  • Copy the following VBA code and paste it into this module window. Then, click on the Run button as shown in the image. You need to edit the saveLocation in the VBA code with your own file directory address on your PC where you want to save the exported pdf.

In this example, C:\Users\sohan\Desktop\Softeko\57-0040\PDF files is the file address to save the exported pdf files. And myPDF1 is the pdf file’s name.

Sub SavingActiveSheetsAsPDF()
Dim saveLocation As String
saveLocation = "C:\Users\sohan\Desktop\Softeko\57-0040\PDF files\myPDF1.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

Macro to Save Active Worksheet as PDF

  • Finally, here is the result. You can easily see the saved location and name of exported pdf file from Excel.

Saving Active Worksheet as PDF


3. Excel Macro to Save Sheets in Separate PDF Files

If you want to save multiple PDFs at a glance, you can use the following VBA code examples to loop through sheets and save each of them as its own pdf.

3.1 Save All Sheets as PDF

A macro code helps you to loop through each Excel sheet from an active workbook and save each sheet as its own pdf. Follow the steps below to apply the macro code.

Steps:

  • First, open an active workbook. Right-click on a sheet name and click on View Code. A new module window will pop up.

Using Loop through Sheets

  • Copy the following VBA code and paste it into the module window. Now, click on the Run button.
Sub LoopSheetsSaveAsPDF()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
Next
End Sub

Macro to Save Active Worksheet as PDF

  • Finally, you will see each sheet saved as its own pdf file. And the files, you will find in the same location where your Excel file is located.

Using Loop through Sheets in Excel Macro to Save as PDF


3.2 Save Selected Sheets as PDF

If you want to save the selected sheets instead of all the sheets of the workbook as pdf, you can do this by following the steps below.

Steps:

  • First, select the sheets one after another by pressing CTRL. Then, right-click on any selected sheet name, and click on View Code. A new module window will pop up.

Using Loop through Selected Sheets in Excel Macro to Save as PDF

  • After that, just copy the following VBA code and paste it into the module.
Sub SelectedSheetsSaveAsPDFByLoop()
Dim ws As Worksheet
Dim sheets_Array As Variant
Set sheets_Array = ActiveWindow.SelectedSheets
For Each ws In sheets_Array
ws.Select
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
Next ws
sheets_Array.Select
End Sub

Macro to Save Active Worksheet as PDF

  • Finally, you will get your pdf files at the same location as the Excel file on your pc.

Using Loop through Selected Sheets in Excel Macro to Save as PDF


4. Saving Selection as PDF

You can also save a particular region from your Excel sheet as a PDF using a macro code. Just follow the steps below.

Steps:

  • First, select the region that you want to save as a pdf. Right-click on the sheet name, then click on View Code. A new module window will pop up.

Saving Selection as PDF

  • Write down the following code into the module window. Then, click on the Run button.
Sub SavingSelectionAsPDF()
Dim FileLocation As String
FileLocation = "C:\Users\sohan\Desktop\Softeko\57-0040\PDF files\mypdf3.pdf"
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FileLocation
End Sub

Saving Selection as PDF with Macro

  • Finally, you will get your pdf in your given location that you wrote into the VBA code at saveLocation part.

Saving Selection as PDF


5. Saving a Range as PDF

You can save a range (In this example, B5:F12) from a particular sheet using a VBA code. To do this, follow the steps below.

Steps:

  • First, go to the sheet that contains the range you need to save as a pdf.

Saving a Range as PDF

  • Now, go to the module window to type the following VBA code modifying with your own save location. After that, click on the Run button.  You can open a new module window either a right-click on the sheet name and then clicking on the View Code or go to Developer >> Visual Basic >> Insert >> Module.
Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim rng As Range
saveLocation = "C:\Users\sohan\Desktop\Softeko\57-0040\PDF files\mypdf4.pdf"
Set rng = Sheets("Sheet1").Range("B5:F12")
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

Saving a Range as PDF with Macro

  • Finally, you will see the pdf file at the location that you put in the code.

Saving a Range as PDF


Conclusion

In this tutorial, I have discussed 6 examples of Excel macro to save as pdf. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo