Knowledge Base

Can't Print Collated Copies

Article ID: 112821

Article Last Modified on 10/11/2006

APPLIES TO

This article was previously published under Q112821

SYMPTOMS

In Microsoft Excel 5.0, when you print multiple copies of a document, you cannot automatically collate the pages unless the printer driver supports collation.

NOTE: In earlier versions of Microsoft Excel, a document can be collated when you print it (regardless of whether the printer driver supports collated printing).

This functionality was added in Microsoft Excel 7.0.

STATUS

This method of printing is by design to allow for faster printing.

WORKAROUND

In Microsoft Excel 5.0, to print collated copies of a file, do any of the following:
  • Print the document to a file on your hard drive and copy the file to the printer multiple times from the MS-DOS prompt. -or-

  • Write a macro to print the selected file multiple times. Note that this option will be very time consuming because Microsoft Excel has to print single copies of the file multiple times. It is much faster to print a file multiple times. (Sample code is provided below.) -or-

  • If your printer driver provides the option to print collated copies, use this option. You can set this option by choosing the Print command on the File menu and then choosing Printer Setup in Microsoft Excel to access the printer driver settings.

MORE INFORMATION

An example of collated printing is when you print two copies of a file such that page 1 and page 2 of the first copy are printed, and then page 1 and page 2 of the second copy are printed. In contrast, uncollated printing would print page 1, and then another page 1, and then page 2, and then another page 2.

To print from the DOS prompt try the following command: 
    copy filename lpt1:/b
where "filename" is the name of your file with its three character extension and lpt1 is the port where your printer is connected. The "/b" stands for binary. Printer drivers that have a Copies box in the Print dialog box may support collation. For example, most UNIDRV-based drivers support collation; some printer drivers, such as the Hewlett-Packard (HP) DeskJet, do not.

SAMPLE VISUAL BASIC FOR APPLICATIONS CODE

The following Visual Basic for Applications code allows you to print multiple copies. 
'A procedure to print multiple copies
 
Sub Example()
   Ncopies = Application.InputBox _
   ("Please enter number of copies to print: ", _
    "Print Multiple Copies", 1, , , , , 1)
   For counter = 1 To Ncopies
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
   Next
End Sub
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Product Support Services (PSS) Professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Additional query words: multiple copies dialogue XL5

Keywords: kbprogramming KB112821


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.