Excel VBA CodeTutorials

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.

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

Related posts

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

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…