I searched for, and tried, numerous ways to export multiple worksheets to a single PDF file with mixed results. I finally learned that Excel would export several worksheets at a time by using the worksheet’s visible property.

This post explains how to implement the Export to PDF routine which is used in the Total Cost Model. The code will take any number of worksheets from an Excel workbook and export them to a single PDF document. Exporting to separate documents, one for each worksheet is fairly easy by using a looping structure to export all of the selected items individually. In this case the selected worksheets, all from the same open workbook, are obtained from a VBA form (shown in the following image.) The form is opened from the custom ribbon toolbar of the Total Cost Model. The routine starts there by selecting the worksheets to export and clicking the “Export” button.

Three things which should be remembered when working with code that hides worksheets and uses Excel’s ExportAsFixedFormat method:

1. At least one worksheet MUST always be visible in an Excel workbook.
2. The ExportAsFixedFormat method, as used in the code, will export all of the visible worksheets.
3. The ExportAsFixedFormat method exports only the Print Area of the worksheet if IgnorePrintAreas=False

The worksheet selections can be made on the form independently of each other or by checking Select All to mark them all. The Test PDF app button runs a test to see if the computer will open a PDF document. Another post shows that code, which is quite similar in its approach.

The Export button click event procedure follows and proceeds according to this pseudocode :

• The selected item found flag, bolFound, is set to False. A Control object is set to the variable name ctrl.
• A For Each loop moves through all of the controls on the form, frmToPDF by reading the ctrl object members.
• An embedded If statement checks for the control TypeName,”CheckBox”.
  • If it finds a checked box, excluding the chkSelectAll checkbox, it sets the flag, bolFound, to True.
• If bolFound is False a message that there was NO selection is displayed,
• Otherwise (else) it calls the ExportToPDF sub procedure…

Private Sub cmdExport_Click()
' find out if there are selected items, and if so go to the export routine

Dim bolFound As Boolean
Dim ctrl As control

On Error Resume Next

    bolFound = False    ' was a checked box found?

    For Each ctrl In frmToPDF.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Name <> "chkSelectAll" Then
                If ctrl.Value = True Then bolFound = True
            End If
        End If
    Next

    If bolFound = False Then   ' if there is NOT anything selected   ' if there is NOT anything selected
        Call MsgBox("There is nothing selected to export!", vbOKOnly, "Nothing selected...")
    Else
        Call ExportToPDF
    End If

End Sub

The following procedure, ExportToPDF, essentially hides the worksheets which are not to be exported to PDF and then uses Excel’s ExportAsFixedFormat method to do the work. The code executes from the first snippet (cmdExport_Click). It is initiated from the line 22, “Call ExportToPDF“. ExportToPDF includes a bit more code than may be necessary. This is because the Total Cost Model workbook has 3 worksheets which may not all be in use (visible) and need to be checked for visibility. At least one System is required and always visible, the other two may be visible or not. The form only enables checkboxes for visible System worksheets, so the routine needs only to return to visibility the ones which were previously visible.

The ExportToPDF sub routine proceeds as explained in this pseudocode:

• Initiate a variable for the System sheet numbers and a loop counter.
• Set the number of visible System worksheets (sysNum) using GetSystemNum(). This a custom function that returns the number of active System worksheets so the routine knows what worksheets to unhide at the end of the export.
• Change the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to False.
• Set the worksheets visibility according to the form’s selection(s) using a For loop.
• Hide the Concepts and Instructions worksheet (It is never set to print or export.)
• Export the visible worksheets using “ThisWorkbook.ExportAsFixedFormat“.
  • The “IgnorePrintAreas:=False” parameter tells the export method to export each worksheet’s Print Area only, if one is set.
• Return the worksheets to their correct visibility, for the Systems worksheets use the var from the first step.
• Unhide the Concepts and Instructions worksheet.
• Return the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to True.
• The error handling routine, errHandler, will trigger if there is a legitimate error (>0), display a message and resume executing the code at the next statement after the error.

Private Sub ExportToPDF()
' first set the selected sheets to visible and the unselected ones to hidden
' then export to PDF,
' then reset the sheets to their proper visibility

Dim sysNum, i As Integer

On Error GoTo errHandler

    sysNum = GetSystemNum() 'get the number of active systems

' The sheets associated with the form checkboxes
'       Common Data         - chkItems1
'       System 1            - chkItems2
'       System 2            - chkItems3
'       System 3            - chkItems4
'       Cost Analysis       - chkItems5
'       Graphical Analysis  - chkItems6

    With Application
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With
        
    ' First set the sheets visibility per the form's selection(s)
    For i = 1 To 6
        Worksheets(i).Visible = Me.Controls("ChkItems" & i).Value
    Next
    
    ' hide the Concepts and Instructions sheet
    Sheets(7).Visible = False
    
    ' Second, export the visible sheets
        ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
                "Total Cost Model - exported.pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
    
    ' Return the sheets to their correct visibility, for the systems use the var from the first step
     For i = 1 To 6
        With Worksheets(i)
                If i = 3 Or i = 4 Then
                    Select Case sysNum
                    Case 1
                        'do nothing...
                    Case 2
                        Sheets(3).Visible = True
                    Case 3
                        Sheets(3).Visible = True
                        Sheets(4).Visible = True
                    End Select
                Else
                    .Visible = True
                End If
        End With
    Next
    
    ' make the Concepts and Instructions sheet visible again
    Sheets(7).Visible = True
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    
errHandler:
    If Err.Number > 0 Then
        MsgBox "Error number: " & Err.Number & " Description: " & Err.Description
        Resume Next
    End If

End Sub

This procedure can be seen in action and the code examined in the workbook, Total Cost Model. You can use this Excel workbook and view all of the code used in it free of charge.