How to Use Excel VBA Macro to Save a PDF in a Specific Folder (7 Examples)

The following dataset contains the sale amounts of different salespeople.

Method 1 – Using Excel VBA Macro to Save Active Worksheet as PDF in a Specific Folder

STEPS:

• Select the destination folder where we want to save the PDF file.
• Click on the option ‘Copy Path’. This will copy the path of the destination folder.

• Go to the Developer tab.
• Select the option Visual Basic from the ribbon.

• A new VBA project window will appear.
• Right-click on the active sheet name.
• Select Insert > Module.

• A blank VBA code window will open.
• Enter 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.
• Click Run or press F5 to run the code.

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

Read More: Excel Macro to Save as PDF

Method 2 – Saving an Active Workbook as a PDF in a Specific Folder with VBA Macro

STEPS:

• Go to the Developer tab > Visual Basic from the workbook.
• Insert a VBA module for the active worksheet, such as example-1.
• A blank code window will appear.
• Enter 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 Run or press F5 to run the code.

• Open the destination folder. We can see our workbook in PDF format in that folder.

Read More: Excel VBA: Save Workbook in Specific Folder

Method 3 – Utilizing Excel VBA Macro to Save the Selected Area as a PDF

STEPS:

• Select cells (A1:C7).

• Go to the Developer tab and select Visual Basic from the workbook.
• Insert a VBA module for the active worksheet.
• A blank code window will appear.
• Enter 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

• Press F5 or click on Run to run the code.

• We can see the PDF format of our selected area in the destination folder.

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

Method 4 – Saving a Selected Range as a PDF in a Specific Folder with VBA in Excel

STEPS:

• Open the workbook and navigate to the Developer tab > Visual Basic.
• Insert a module similar to example-1 for the active worksheet.
• A blank VBA code window will show up.
• Enter the following formula in the blank code window:

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

• To run the code, click Run or press F5.

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

Method 5 – Using Excel VBA Macro to Save a Chart as a PDF

STEPS:

• Click on the chart.
• Go to the Chart Design Note the name of the chart which is Chart 1.

• Open the workbook and go to Developer > Visual Basic.
• Insert a VBA module for that worksheet.
• We will get a blank code window.
• In the blank code window, enter 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

• Click Run or press F5.

• We get a new PDF file in our destination folder named Chart.

• If we open that PDF file, we will see the chart on the worksheet.

Method 6 – Using a Loop with Excel VBA to Save a PDF

6.1 Apply Loop Through All Worksheets

STEPS:

• Open the workbook and proceed to the Developer tab > Visual
• Insert a VBA module for the active worksheet like example-1.
• A VBA code window will open.
• 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

• Press F5 or click Run.

• Go to the folder in our source Excel worksheet.
• We can see all the worksheets of our workbook in PDF format.

6.2 Insert a Loop Through Selected Worksheets

STEPS:

• Select three worksheets. The names of the worksheets are Chart, Loop, and Loop-1.

• Go to the Developer tab > Visual Basic.
• Insert a VBA module for the active worksheet.
• A blank VBA code window will open.
• Enter 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

• Click Run or press F5.

• Go to the source folder of our Excel We get the PDF formats of our selected worksheets.

Method 7 – Selecting a Specific Folder Manually to Save Worksheets as a PDF with VBA Macro

STEPS:

• Go to the Developer tab > Visual Basic.
• Insert a VBA module for the active worksheet like example-1.
• We can see a blank VBA code window.
• 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

• Press F5 or click Run.

• The above action will open the ‘Select Folder and FileName to save’ dialogue box.
• We can browse different locations from this dialogue box to save that file. We have browsed the location of our destination folder.
• Rename that file. We renamed the file to Manually.
• Click on OK.

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

• We can see a new PDF file named Manually in our destination folder.

Download the Practice Workbook

You can download the practice workbook from here.

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