Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)

This tutorial will demonstrate different examples to use Excel VBA macro to save as PDF in a specific folder. We can export or save an Excel sheet as a pdf file in Excel 2010 or later versions. The codes that we will use in the examples will save one or multiple Excel sheets in a specific folder.

Excel VBA Macro to Save PDF in Specific Folder: 7 Ideal Examples

Throughout this tutorial, we will illustrate 7 different examples of using Excel VBA macro to save as  PDF in a specific folder. To illustrate all the examples we will use the following dataset. The dataset contains sales amounts of different salespeople.

1. Excel VBA Macro to Save Active Worksheet as PDF in Specific Folder

First and foremost, we will use Excel macro to save the active worksheet of our workbook as a PDF in a specific folder. Our workbook might contain several worksheets. But in this example, we will only save the active worksheet as a PDF. Let’s see the steps to perform this action.

STEPS:

• To begin with, select the destination folder where we want to save the PDF file.
• In addition, click on the option ‘Copy Path’. This we copy the path of the destination folder.

• Furthermore, go to the Developer tab.
• Then, select the option Visual Basic from the ribbon.

• A new VBA project window will appear.
• Next, right-click on the active sheet name.
• Moreover, select Insert > Module.

• So, the above will open a blank VBA code window.
• Afterward, type the following code in that code window:

Sub Save_Worksheet()
Dim LocationSave As String
LocationSave = "C:\Users\User\Documents\Destination\ExcelDemy\WorksheetSales.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=LocationSave
End Sub

• The highlighted part of the code is the path of our destination folder that we copied earlier.
• Now, click on the Run button or press the F5 key to run the code.

• Finally, open the destination folder. We can see the active worksheet of our workbook in PDF format.

Read More: Excel Macro to Save as PDF

2. Save Active Workbook as PDF in Specific Folder with VBA Macro

In the second example, we will use Excel VBA macro to save the workbook as a PDF in a specific folder. The code in this example will save all the worksheets of the active workbook in PDF format. To do this follow the below steps.

STEPS:

• First, go to the Developer tab > Visual Basic from the workbook.
• Next, insert a VBA module for the active worksheet like the example-1.
• A blank code window will appear.
• Then type the following code in the blank code window:

Sub Save_Workbook()
Dim LocationSave As String
LocationSave = "C:\Users\User\Documents\Destination\ExcelDemy\WorkbookSales.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=LocationSave
End Sub

• Click on the Run button or press the F5 key to run the code.

• In the end, open the destination folder. We can see the PDF format of our workbook in that folder.

Read More: Excel VBA: Save Workbook in Specific Folder

3. Utilize Excel VBA Macro to Save Selected Area as PDF

In the third example, we will use the Excel VBA macro to save selected areas of a worksheet as a PDF in a specific folder. Sometimes we may need to save only a specific part of our worksheet. That’s why we will apply this code. Follow the below steps to perform this example.

STEPS:

• Firstly, select cells (A1:C7).

• Secondly, go to the Developer tab and select Visual Basic from the workbook.
• Thirdly, like the example-1 insert a VBA module for the active worksheet.
• So, a blank code window will appear.
• Then, type the following code in the blank code window:

Sub Save_Selected_Area()
Dim LocationSave As String
LocationSave = "C:\Users\User\Documents\Destination\ExcelDemy\AreaSales.pdf"
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=LocationSave
End Sub

• Now, press the F5 key or click on the Run button to run the code.

• Lastly, we can see the PDF format of our selected area in the destination folder.

Read More: Excel VBA: Choose Location and Save as PDF

4. Save Selected Range as PDF in Specific Folder with VBA in Excel

This example is similar to the previous one. In the previous example, we selected the area manually whereas in this example we will specify the area using the range in the code. So, in this example, we will use Excel VBA macro to save a range of our worksheets as a PDF in a specific folder. Just follow the below steps to perform this.

STEPS:

• In the beginning, open the workbook and navigate to the Developer tab > Visual Basic.
• Then, insert a module similar to example-1 for the active worksheet.
• So, a blank VBA code window will show up.
• After that, in the blank code window, type the following code:

Sub Save_Range_to_PDF()
Dim LocationSave As String
Dim rg As Range
LocationSave = "C:\Users\User\Documents\Destination\ExcelDemy\RangeSales.pdf"
Set rg = Sheets("Range").Range("A1:C7")
rg.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=LocationSave
End Sub

• Now, to run the code, click on the Run button or press the F5 key.

• As a result, in the destination folder, we get our desired PDF format.

5. Excel VBA Macro to Save Chart as PDF

This example will use Excel macro to save a specified chart of a worksheet as a PDF in a specific folder. In the following dataset, we can see a chart based on the sales data. We will save that chart in PDF format.

Let’s see the steps to do this method.

STEPS:

• To begin with, click on the chart.
• In addition, go to the Chart Design Note the name of the chart which is Chart 1.

• Furthermore, open the workbook and go to Developer > Visual Basic.
• Then, like in example-1, insert a VBA module for that worksheet.
• So, we will get a blank code window.
• Afterward, in the blank code window, type the following code:

Sub Chart_to_PDF()
Dim LocationSave As String
Dim crt As Chart
LocationSave = "C:\Users\User\Documents\Destination\ExcelDemy\Chart.pdf"
Set crt = Sheets("Chart").ChartObjects("Chart 1").Chart
crt.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=LocationSave
End Sub

• After that, click on the Run button or press the F5 key.

• So, we get a new PDF file in our destination folder named Chart.

• Finally, if we open that PDF file we will see the chart from the worksheet.

6. Use Loop with Excel VBA to Save PDF

Generally, a loop is used to do multiple tasks quickly. If we apply a loop to do any particular work it saves a lot of time for us. In this example, we will apply a loop with Excel VBA macro to save PDF in a specific folder.

6.1 Apply Loop Through All Worksheets

In this example, we will apply a loop for all the worksheets of our workbook. So the macro that we will use will loop through each worksheet in the active workbook. Then it will save each worksheet as its own PDF. Here, unlike in the previous examples, the PDF files will save in the folder that contains the source workbook. Go through the following steps to perform this example.

STEPS:

• Firstly, open the workbook and proceed to the Developer tab > Visual
• Secondly, insert a VBA module for the active worksheet like example-1.
• So, a VBA code window will open.
• Thirdly, insert the following code in that code window:

Sub Loop_to_Save()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & wsheet.Name & ".pdf"
Next
End Sub

• After that, press the F5 key or click on the Run button.

• Now, go to the folder of our source excel worksheet.
• Lastly, we can see all the worksheets of our workbook in PDF format.

6.2 Insert Loop Through Selected Worksheets

This example is just the extended version of the previous one. What if we want to use VBA macro to insert a loop to save only selected worksheets from our workbook? Let’s see the steps to apply the loop to save selected worksheets as a PDF.

STEPS:

• First, we will select three worksheets. The names of the worksheets are Chart, Loop, and Loop-1.

• Next, go to the Developer tab > Visual Basic.
• Then, like example-1 insert a VBA module for the active worksheet.
• A blank VBA code window will appear.
• After that, type the following code in that code window:

Sub Loop_to_Save_Selected_Sheet()
Dim wsheet As Worksheet
Dim ArraySheet As Variant
Set ArraySheet = ActiveWindow.SelectedSheets
For Each wsheet In ArraySheet
wsheet.Select
wsheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & wsheet.Name & ".pdf"
Next wsheet
ArraySheet.Select
End Sub

• Now, click on the Run button or press the F5 key.

• Finally, go to the source folder of our Excel We get the PDF formats of our selected worksheets.

7. Select Specific Folder Manually to Save Worksheets as PDF with VBA Macro

So far, we have used a macro to save worksheets as a PDF in a predefined folder. We can choose the destination of the PDF file spontaneously from the code of this example. To perform this method just follow the below steps.

STEPS:

• In the first place, go to the Developer tab > Visual Basic.
• Next, insert a VBA module for the active worksheet like example-1.
• So, we can see a blank VBA code window.
• Then, enter the following code in that code window:

Sub Select_Location_Manually()
Dim wsheet As Worksheet
Dim wBook As Workbook
Dim Timestr As String
Dim Namestr As String
Dim PathStr As String
Dim strFile As String
Dim PathStrFile As String
Dim File As Variant
On Error GoTo errHandler
Set wBook = ActiveWorkbook
Set wsheet = ActiveSheet
Timestr = Format(Now(), "yyyymmdd\_hhmm")
PathStr = wBook.Path
If PathStr = "" Then
PathStr = Application.DefaultFilePath
End If
PathStr = PathStr & "\"
Namestr = Replace(wsheet.Name, " ", "")
Namestr = Replace(Namestr, ".", "_")
strFile = Namestr & "_" & Timestr & ".pdf"
PathStrFile = PathStr & strFile
File = Application.GetSaveAsFilename _
(InitialFileName:=PathStrFile, FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
If File <> "False" Then
wsheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=File, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "Created PDF file: " & vbCrLf & File
End If
exitHandler:
Exit Sub
errHandler:
MsgBox "Unable to Create PDF file"
Resume exitHandler
End Sub

• Now, press the F5 key or click on the Run button.

• The above action will open the ‘Select Folder and FileName to save’ dialogue box.
• From this dialogue box, we can browse different locations to save that file. We have browsed the location of our destination folder.
• Also, we can rename that file. We renamed the file to Manually.
• After that, click on OK.

• A new popup window will appear. Click on OK from that window.

• In the end, we can see a new PDF file named Manually in our destination folder.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this tutorial is a comprehensive overview of using Excel VBA macro to save as PDF in a specific folder.  Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. We’ll do our best to respond as soon as possible. Keep an eye on our website for more interesting Microsoft Excel solutions in the future.

Related Articles

• Excel VBA: Create Invoice and Save PDF Format
• Excel Macro: Save as PDF with Date in Filename
• Excel Macro to Save as PDF with Filename from Cell Value