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

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.