Guide to save sheets in same Excel workbook to separate PDFs

Saving Excel sheets as separate PDFs can be essential for organizing data, sharing specific information without exposing the entire workbook, and meeting various reporting requirements. However, users often face challenges when trying to manually save each sheet, such as time consumption and the risk of errors. Automating this process using VBA not only simplifies the task but also ensures accuracy and efficiency. This tutorial will guide you through creating an interactive VBA macro that allows you to select a folder and save each sheet in your workbook as a separate PDF, streamlining your workflow and enhancing productivity.

1. Open the Excel workbook you want to save sheets from.

2. Press Alt + F11 to open the VBA editor.

3. Go to Insert > Module to create a new module.

4. Copy and paste the below code into the module.

Sub SaveSheetsAsPDFs()
    Dim ws As Worksheet
    Dim FilePath As String
    Dim FileName As String
    Dim FolderPath As String
    Dim FolderDialog As FileDialog
    Dim FolderSelected As Integer
    
    ' Create a FileDialog object as a folder picker dialog
    Set FolderDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    ' Set dialog properties
    FolderDialog.Title = "Select Folder to Save PDFs"
    FolderDialog.AllowMultiSelect = False
    
    ' Show the dialog
    FolderSelected = FolderDialog.Show
    
    ' Check if a folder was selected
    If FolderSelected = -1 Then
        FolderPath = FolderDialog.SelectedItems(1) & "\"
    Else
        MsgBox "No folder selected. Exiting."
        Exit Sub
    End If
    
    ' Loop through each worksheet
    For Each ws In ThisWorkbook.Worksheets
        ' Set the file name as the worksheet name
        FileName = ws.Name & ".pdf"
        ' Combine folder path and file name
        FilePath = FolderPath & FileName
        
        ' Save the worksheet as PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
    Next ws
    
    MsgBox "Sheets have been saved as PDFs in " & FolderPath
End Sub

5. Press F5 or run the macro from the Developer tab in Excel to execute it.

6. A dialog will appear asking you to select the folder where you want to save the PDFs.

7. Select the folder and click OK.The macro will save each sheet as a separate PDF in the selected folder.