NEW! FREE 15 Hours Microsoft Excel Course! View 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
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…
ExcelTutorials

220+ MS Excel Shortcuts Keys in PDF | Excel Keyboard Shortcuts

11 Mins read
MS Excel Shortcuts Keys, when starting with Microsoft Excel, knowing a few ms excel shortcuts keys will reduce your work time and…
ExcelTutorials

How to Find Duplicate Values in Excel

2 Mins read
You can Find duplicate values in Excel by reading this blog. An Excel sheet is a great way of packing lots of…