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.
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.