NEW! FREE 90 Minutes Excel Charts Course for Reporting Watch now 

ExcelExcel VBA Code

Save Each Worksheet as a Separate PDF

1 Mins read

Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window.

Step 2: In the Visual Basic for Applications window, Click Insert > Module, then Copy the below VBA code into the code window.

Sub ExportEachSheet2PDF()
'Created by Yoda Learning Academy
'This VBA code will export each sheet of the workbook
'to a PDF file
'
Dim objWS As Worksheet
Dim strFileName As String
Dim strPath As String
Dim strCrntWS As String

strPath = ThisWorkbook.Path 'Assigned current file path
strCrntWS = ActiveSheet.Name 'Assigned current sheetname

Application.ScreenUpdating = False 'Disabled screen updating

For Each objWS In Worksheets
objWS.Select

strFileName = objWS.Name & ".pdf" 'Assigned worsksheet name as filename

'Following code will put '\' if it is not there
'-----------------------------------------------
If VBA.Right(strPath, 1) <> Application.PathSeparator Then
strPath = strPath & Application.PathSeparator
End If

'The following code will decide the export file type and then export it
'to the given path considering other required parameters.
'-----------------------------------------------------------------------
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=strPath & strFileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False 'This will prevent to open the file after publishing
Next objWS

Worksheets(strCrntWS).Select 'After exporting all the worksheets, it will return to active sheet
Application.ScreenUpdating = True 'Enabled screen updating

MsgBox "All sheets have been exported as PDF.", vbInformation + vbOKOnly, "Exported as PDF"
End Sub

Step 3: Press the F5 key or click the Run button to run the code.

Related posts
Excel VBA Code

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…
ExcelExcel VBA Code

VBA Code to Split table by Columns

3 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…
ExcelExcel VBA Code

Create A Table Of Contents By VBA Code

1 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…