Print pages in collated order

[Guest]

I have a program that I am putting together...it allows a user to use a ToggleButton to print out a variable number of pages depending on how many products have been chosen.

The problem is when the pages print, they come out by individual product, not collated sets (e.g., if I want to print six copies of each product it will print out six copies of product number one, six of number two and so on). I want to be able to print them in order (e.g., one of product one, one of product two, etc…so that eventually six full sets are printed out).

Here are the string of commands that I have so far...its long but repetitive. The various sub routines are only used depending on how many products have been selected up to a maximum of six (i.e., one P&L per product * six products).

This is confusing stuff to write about so please let me know if you need further clarificatio

Private Sub CommandButton1_Click(
If Sheets("Start").ToggleButton1.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("D2:F52").Selec
ActiveSheet.PageSetup.PrintArea = "$D$2:$F$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

If Sheets("Start").ToggleButton2.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("I2:K52").Selec
ActiveSheet.PageSetup.PrintArea = "$I$2:$K$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

If Sheets("Start").ToggleButton3.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("N252").Selec
ActiveSheet.PageSetup.PrintArea = "$N$2:$P$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

If Sheets("Start").ToggleButton4.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("S2:U52").Selec
ActiveSheet.PageSetup.PrintArea = "$S$2:$U$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

If Sheets("Start").ToggleButton5.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("X2:Z52").Selec
ActiveSheet.PageSetup.PrintArea = "$X$2:$Z$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

If Sheets("Start").ToggleButton6.Value = True The
Sheets("P&L").Selec
Sheets("P&L").Range("AC2:AE52").Selec
ActiveSheet.PageSetup.PrintArea = "$AC$2:$AE$52
Sheets("P&L").Selec
Sheets("P&L").Range("B2").Selec
ActiveWindow.SelectedSheets.PrintOut Copies:=Range("E5").Value, Collate:=Tru
End I

Sheets("Print Instructions").Selec
Sheets("Print Instructions").Range("C2").Selec
End Sub

 

[Dave Peterson]

I think that this'll work:

Option Explicit
Private Sub Comman3dButton1_Click()

Dim myPrintArea As String

myPrintArea = ""
If Sheets("Start").ToggleButton1.Value = True Then
myPrintArea = myPrintArea & "$D$2:$F$52" & ","
End If

If Sheets("Start").ToggleButton2.Value = True Then
myPrintArea = myPrintArea & "$I$2:$K$52" & ","
End If

If Sheets("Start").ToggleButton3.Value = True Then
myPrintArea = myPrintArea & "$N$2:$P$52" & ","
End If

If Sheets("Start").ToggleButton4.Value = True Then
myPrintArea = myPrintArea & "$S$2:$U$52" & ","
End If

If Sheets("Start").ToggleButton5.Value = True Then
myPrintArea = myPrintArea & "$X$2:$Z$52" & ","
End If

If Sheets("Start").ToggleButton6.Value = True Then
myPrintArea = myPrintArea & "$AC$2:$AE$52" & ","
End If

If myPrintArea = "" Then
MsgBox "Nothing to print"
Else
With Sheets("P&L")
.PageSetup.PrintArea = Left(myPrintArea, Len(myPrintArea) - 1)
.PrintPreview
' .PrintOut Copies:=Worksheets("Start").Range("E5").Value, _
Collate:=True
End With
End If

Sheets("Print Instructions").Select
Sheets("Print Instructions").Range("C2").Select

End Sub

But it looks like the data/togglebuttons (and names) are laid out nicely:

Option Explicit
Private Sub CommandButton1_Click()

Dim myPrintArea As String
Dim iCtr As Long
Dim myAddresses(1 To 6) As String

myAddresses(1) = "$D$2:$F$52"
myAddresses(2) = "$I$2:$K$52"
myAddresses(3) = "$N$2:$P$52"
myAddresses(4) = "$S$2:$U$52"
myAddresses(5) = "$X$2:$Z$52"
myAddresses(6) = "$AC$2:$AE$52"

myPrintArea = ""
For iCtr = LBound(myAddresses) To UBound(myAddresses)
If Sheet1.OLEObjects("togglebutton" & iCtr).Object.Value = True Then
myPrintArea = myPrintArea & myAddresses(iCtr) & ","
End If
Next iCtr

If myPrintArea = "" Then
MsgBox "Nothing to print"
Else
With Sheets("P&L")
.PageSetup.PrintArea = Left(myPrintArea, Len(myPrintArea) - 1)
.PrintPreview
' .PrintOut Copies:=Worksheets("Start").Range("E5").Value, _
Collate:=True
End With
End If

Sheets("Print Instructions").Select
Sheets("Print Instructions").Range("C2").Select
End Sub
But it looks like the data/togglebuttons are laid out nicely:

 

[Guest]

Dave,

Thanks for the input

I used your first routine with a few slight changes. Most notably, I deleted the command relating to PrintPreview as the macro kept getting hung up over this.

Here it is in case you care..

Private Sub CommandButton1_Click(
Dim myPrintArea As Strin

myPrintArea = "
If Sheets("Start").ToggleButton1.Value = True The
myPrintArea = myPrintArea & "$D$2:$F$52" & ",
End I

If Sheets("Start").ToggleButton2.Value = True The
myPrintArea = myPrintArea & "$I$2:$K$52" & ",
End I

If Sheets("Start").ToggleButton3.Value = True The
myPrintArea = myPrintArea & "$N$2:$P$52" & ",
End I

If Sheets("Start").ToggleButton4.Value = True The
myPrintArea = myPrintArea & "$S$2:$U$52" & ",
End I

If Sheets("Start").ToggleButton5.Value = True The
myPrintArea = myPrintArea & "$X$2:$Z$52" & ",
End I

If Sheets("Start").ToggleButton6.Value = True The
myPrintArea = myPrintArea & "$AC$2:$AE$52" & ",
End I

If myPrintArea = "" The
MsgBox "Nothing to print
Els
With Sheets("P&L"
.PageSetup.PrintArea = Left(myPrintArea, Len(myPrintArea) - 1
.PrintOut Copies:=Worksheets("Print Instructions").Range("E5").Value, Collate:=Tru
End Wit
End I

Sheets("Print Instructions").Selec
Sheets("Print Instructions").Range("C2").Select

 

[Dave Peterson]

I'm not sure why the printpreview caused hangups in excel, but I usually test
with printpreviews to save some trees.

Glad you got it working.