Printing multiple workbooks at once

[kingnutin]

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)
        Windows(SheetName).Activate
        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!

 

[sweater_vests_rock]

kingnutin.

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.