Problem printing multiple sheets to one PDF file

Bean Dinner

New Member
Aug 10, 2014
Using Excel 2007 in Windows 10, but had the same problem in Vista. Have a workbook with about 50 sheets, half are data sheets, half are charts based on the data. To print only the charts, I hide all data sheets, right-click on a chart sheet and select all sheets. In print preview, I see 68 pages, & can scroll through all 68. But when I click print, either to a printer or to PDF, instead of printing or saving all in one 68-page batch, they print or save in smaller batches of varying page counts. To get one PDF file of all 68 pages, I have to save several times and then merge the files. Any ideas why, and what I can do?


Well-known Member
Oct 15, 2013
Welcome to the forum....

Here's some code from Siddharth Rout that may help.
Option Explicit

Sub Sample()
    Dim ws As Worksheet, wsTemp As Worksheet
    Dim chrt As Shape
    Dim tp As Long
    Dim NewFileName As String

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    NewFileName = Application.GetSaveAsFilename( _
        fileFilter:="PDF Files (*.pdf), *.pdf")

    Set ws = Sheets("Status and SLA trends")
    Set wsTemp = Sheets.Add

    tp = 10

    With wsTemp
        For Each chrt In ws.Shapes
            Selection.Top = tp
            Selection.Left = 5
            tp = tp + Selection.Height + 50
    End With

    If NewFileName <> "False" Then
        wsTemp.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFileName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End If

    Application.DisplayAlerts = False

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    Exit Sub
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...