Printing multiple workbooks at once


Board Regular
Jun 15, 2005
I currently have and Excel file that is about 1.5mb in size. The file is too big, and I need to make it smaller so that it can be opened faster. This document has close to 80 different worksheets. I have a GUI that opens on the main page and gives a selection of what sheets the user wants to print. Based on the selection, the user can print to any printer installed on their machine.

I have seperated all the printable sheets into their own Excel file, so that my main file now only has 5 worksheets and is at 220kb.

Now the BIG problem! I have rewritten the code so that when a user selects a single sheet it prints just as before buy opening the selected sheet(now a workbook), printing and then closing that opened workbook.
Sub PrintSheet(SheetName As String)
FilePath = "C:\Documents and Settings\user\My Documents\My Designs\Forms\Test Documents\"
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
        Workbooks.Open (FilePath + SheetName)
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        ActiveWindow.Close SaveChanges:=False
    End If
End Sub

Sometimes the user need to print a range of sheets at once. The method used before was that the printer would print a range of sheets that were listed in a named range.
For example: The user selects to print a New Patient Packet. In an Excel named range of New Patient Packet, would contain a list of all sheets needed to be printed.

I cannot get this to work now that I have seperated the sheets into their own workbooks. All the workbooks need to be selected and printed at the same time because if a user chooses to print to a pdf, all pages must be in the same pdf file and not multiple pdf's. Again, this code worked perfect before I split the sheets into their own workbooks.
Private Sub FilePrepPrint(PrepRange)
    With Worksheets("File Prep Config")
        temp = Join(WorksheetFunction.Transpose(PrepRange), ",")
        varr = Split(temp, ",")
    End With
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
        Sheets(varr).PrintOut Copies:=1, Collate:=True
    End If
End Sub

I figure that this code has four problems:
1. If the list of documents to be opened only contains the sheetnames, how to make the path direct to the FilePath variable. i.e.Range contains book1.xls, book2.xls, book3.xls.... but open FilePath + book1.xls, FilePath + book2.xls....
2. How to make it so that multiple worksheets are opened from the range that is passed to the subroutine.
3. How to print multiple workbooks that are opened at once.
4. How to close those newly opened workbooks.

Any and all ideas and comments are greatly appreciated!

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.


Well-known Member
Oct 8, 2004

have you considered opening each file, selecting the appropriate worksheets, moving them into a common file, and then print? this would replicate your previous solution, only it avoids the issue of an overly large document. the nice part is it would take very little additional coding :)

hope this helps. ben.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back