Hi -
I have multiple workbooks containing 60 printable graph pages each. In the
front of each workbook I have a "table of contents" worksheet where a user
can click a check box for whichever of the 60 graphs they need to print at a
given time. This works great except that the way I've written the macro,
each selection goes to the printer individually. Is there a way to group the
numerous selections a user may have checked and send them to the printer as
just one print job? Here's an example of the working macro as currently
written--

Print Graphs Macro:

Dim Copies As Integer

Dim TOC As String, R12G As String, PVCG As String, M36G As String
TOC = "Table of Contents"
R12G = "Rolling 12 Graphs"
PVCG = "Premium_vs_Claims_Graphs"
M36G = "Monthly_36_Graphs"

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

Application.ScreenUpdating = False

With Sheets(TOC)
Copies = .CopyCount.Value
If .P1RT.Value = True Then
Sheets(R12G).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If
If .P1PC.Value = True Then
Sheets(PVCG).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If

Note: The "P1RT" and "P1PC" represent names given to the check boxes. I
have 60 checkboxes and am only showing the commands for two of them here, to
save space.

Thanks in advance for any ideas!
Susan

If you can build an array of those sheetnames, you can print that as a group.

Try selecting a few sheets (click on the first and ctrl-click on subsequent).

Then print that test.

If that prints as one job, maybe you build that array of names in code:

Option Explicit
Sub testme()

Dim mySheetNames() As String
Dim iCtr As Long
Dim wCtr As Long

iCtr = 0
For wCtr = 1 To Sheets.Count
If wCtr Mod 2 = 0 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Sheets(wCtr).Name
End If
Next wCtr

If iCtr = 0 Then
'do nothing
Else
Sheets(mySheetNames).PrintOut preview:=True
End If

End Sub

This example just looked for even numbered worksheets. But you could loop
through your checkboxes and build that array.





SueDot wrote:

Hi -
I have multiple workbooks containing 60 printable graph pages each. In the
front of each workbook I have a "table of contents" worksheet where a user
can click a check box for whichever of the 60 graphs they need to print at a
given time. This works great except that the way I've written the macro,
each selection goes to the printer individually. Is there a way to group the
numerous selections a user may have checked and send them to the printer as
just one print job? Here's an example of the working macro as currently
written--

Print Graphs Macro:

Dim Copies As Integer

Dim TOC As String, R12G As String, PVCG As String, M36G As String
TOC = "Table of Contents"
R12G = "Rolling 12 Graphs"
PVCG = "Premium_vs_Claims_Graphs"
M36G = "Monthly_36_Graphs"

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

Application.ScreenUpdating = False

With Sheets(TOC)
Copies = .CopyCount.Value
If .P1RT.Value = True Then
Sheets(R12G).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If
If .P1PC.Value = True Then
Sheets(PVCG).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If

Note: The "P1RT" and "P1PC" represent names given to the check boxes. I
have 60 checkboxes and am only showing the commands for two of them here, to
save space.

Thanks in advance for any ideas!
Susan

--

Dave Peterson

Dave,
Thanks for your quick response - however, I think I wasn't clear about how
my charts are set up - I have 20 charts per sheet, and a total of 3
worksheets in a book. The table of contents page has 60 checkboxes that are
linked to each of the 60 charts. A user can choose to select any one or any
number of the 60 charts and then print them. What I need is a way to go to
the various pages on each of the 3 worksheets and only print those that have
been selected and print them in one print job. I'm not even sure if that's
possible. Since your answer selects the printing of an entire sheet, is
there a way to have it just selectively choose which pages it will print off
that sheet?

"Dave Peterson" wrote:

If you can build an array of those sheetnames, you can print that as a group.

Try selecting a few sheets (click on the first and ctrl-click on subsequent).

Then print that test.

If that prints as one job, maybe you build that array of names in code:

Option Explicit
Sub testme()

Dim mySheetNames() As String
Dim iCtr As Long
Dim wCtr As Long

iCtr = 0
For wCtr = 1 To Sheets.Count
If wCtr Mod 2 = 0 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Sheets(wCtr).Name
End If
Next wCtr

If iCtr = 0 Then
'do nothing
Else
Sheets(mySheetNames).PrintOut preview:=True
End If

End Sub

This example just looked for even numbered worksheets. But you could loop
through your checkboxes and build that array.





SueDot wrote:

Hi -
I have multiple workbooks containing 60 printable graph pages each. In the
front of each workbook I have a "table of contents" worksheet where a user
can click a check box for whichever of the 60 graphs they need to print at a
given time. This works great except that the way I've written the macro,
each selection goes to the printer individually. Is there a way to group the
numerous selections a user may have checked and send them to the printer as
just one print job? Here's an example of the working macro as currently
written--

Print Graphs Macro:

Dim Copies As Integer

Dim TOC As String, R12G As String, PVCG As String, M36G As String
TOC = "Table of Contents"
R12G = "Rolling 12 Graphs"
PVCG = "Premium_vs_Claims_Graphs"
M36G = "Monthly_36_Graphs"

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

Application.ScreenUpdating = False

With Sheets(TOC)
Copies = .CopyCount.Value
If .P1RT.Value = True Then
Sheets(R12G).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If
If .P1PC.Value = True Then
Sheets(PVCG).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If

Note: The "P1RT" and "P1PC" represent names given to the check boxes. I
have 60 checkboxes and am only showing the commands for two of them here, to
save space.

Thanks in advance for any ideas!
Susan

--

Dave Peterson

You could copy the charts and paste them as pictures.

Manually, if you select the chart, then shift-click on Edit, you'll see "Copy
Picture".

Then you could paste the picture into a new worksheet and stack them one below
the other.

I'm not sure how your dialog selects the charts, but maybe something like this
will give you an idea:

Option Explicit
Sub testme()
Dim ChtObj As ChartObject
Dim myTop As Double
Dim myPict As Picture
Dim prtWks As Worksheet

Set prtWks = Worksheets.Add

myTop = 0
For Each ChtObj In Worksheets("sheet1").ChartObjects
If Right(ChtObj.Name, 1) = "3" _
Or Right(ChtObj.Name, 1) = "5" Then
ChtObj.CopyPicture Appearance:=xlScreen, Format:=xlPicture
With prtWks
.Paste
Set myPict = .Pictures(.Pictures.Count)
myPict.Left = 0
myPict.Top = myTop
myPict.Width = ChtObj.Width
myPict.Height = ChtObj.Height
myTop = myTop + ChtObj.Height
End With
End If
Next ChtObj

End Sub

(I put a bunch of charts on a Sheet1 and then just wanted to copy then charts
that had names that ended with 3 or 5. (my way of testing just a partial set of
charts.)


SueDot wrote:

Dave,
Thanks for your quick response - however, I think I wasn't clear about how
my charts are set up - I have 20 charts per sheet, and a total of 3
worksheets in a book. The table of contents page has 60 checkboxes that are
linked to each of the 60 charts. A user can choose to select any one or any
number of the 60 charts and then print them. What I need is a way to go to
the various pages on each of the 3 worksheets and only print those that have
been selected and print them in one print job. I'm not even sure if that's
possible. Since your answer selects the printing of an entire sheet, is
there a way to have it just selectively choose which pages it will print off
that sheet?

"Dave Peterson" wrote:

If you can build an array of those sheetnames, you can print that as a group.

Try selecting a few sheets (click on the first and ctrl-click on subsequent).

Then print that test.

If that prints as one job, maybe you build that array of names in code:

Option Explicit
Sub testme()

Dim mySheetNames() As String
Dim iCtr As Long
Dim wCtr As Long

iCtr = 0
For wCtr = 1 To Sheets.Count
If wCtr Mod 2 = 0 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Sheets(wCtr).Name
End If
Next wCtr

If iCtr = 0 Then
'do nothing
Else
Sheets(mySheetNames).PrintOut preview:=True
End If

End Sub

This example just looked for even numbered worksheets. But you could loop
through your checkboxes and build that array.





SueDot wrote:

Hi -
I have multiple workbooks containing 60 printable graph pages each. In the
front of each workbook I have a "table of contents" worksheet where a user
can click a check box for whichever of the 60 graphs they need to print at a
given time. This works great except that the way I've written the macro,
each selection goes to the printer individually. Is there a way to group the
numerous selections a user may have checked and send them to the printer as
just one print job? Here's an example of the working macro as currently
written--

Print Graphs Macro:

Dim Copies As Integer

Dim TOC As String, R12G As String, PVCG As String, M36G As String
TOC = "Table of Contents"
R12G = "Rolling 12 Graphs"
PVCG = "Premium_vs_Claims_Graphs"
M36G = "Monthly_36_Graphs"

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

Application.ScreenUpdating = False

With Sheets(TOC)
Copies = .CopyCount.Value
If .P1RT.Value = True Then
Sheets(R12G).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If
If .P1PC.Value = True Then
Sheets(PVCG).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If

Note: The "P1RT" and "P1PC" represent names given to the check boxes. I
have 60 checkboxes and am only showing the commands for two of them here, to
save space.

Thanks in advance for any ideas!
Susan

--

Dave Peterson



--

Dave Peterson

Sadly, I think I'm having too hard a time explaining my problem well enough
that someone unaquainted with it can figure out what I'm doing.
Unfortunately, the second approach you've outlined won't work for me, either.
After discussing it with others in my office, we've decided it just isn't
that big a deal to send 20 separate print jobs vs. one print job of 20 pages
so I'm going to leave the macros as they presently are. I can see you put a
lot of time and thought into your replies to me and I truly appreciate your
efforts. I'll keep them on file for future possibilities. Thanks so much
for trying!

"Dave Peterson" wrote:

You could copy the charts and paste them as pictures.

Manually, if you select the chart, then shift-click on Edit, you'll see "Copy
Picture".

Then you could paste the picture into a new worksheet and stack them one below
the other.

I'm not sure how your dialog selects the charts, but maybe something like this
will give you an idea:

Option Explicit
Sub testme()
Dim ChtObj As ChartObject
Dim myTop As Double
Dim myPict As Picture
Dim prtWks As Worksheet

Set prtWks = Worksheets.Add

myTop = 0
For Each ChtObj In Worksheets("sheet1").ChartObjects
If Right(ChtObj.Name, 1) = "3" _
Or Right(ChtObj.Name, 1) = "5" Then
ChtObj.CopyPicture Appearance:=xlScreen, Format:=xlPicture
With prtWks
.Paste
Set myPict = .Pictures(.Pictures.Count)
myPict.Left = 0
myPict.Top = myTop
myPict.Width = ChtObj.Width
myPict.Height = ChtObj.Height
myTop = myTop + ChtObj.Height
End With
End If
Next ChtObj

End Sub

(I put a bunch of charts on a Sheet1 and then just wanted to copy then charts
that had names that ended with 3 or 5. (my way of testing just a partial set of
charts.)


SueDot wrote:

Dave,
Thanks for your quick response - however, I think I wasn't clear about how
my charts are set up - I have 20 charts per sheet, and a total of 3
worksheets in a book. The table of contents page has 60 checkboxes that are
linked to each of the 60 charts. A user can choose to select any one or any
number of the 60 charts and then print them. What I need is a way to go to
the various pages on each of the 3 worksheets and only print those that have
been selected and print them in one print job. I'm not even sure if that's
possible. Since your answer selects the printing of an entire sheet, is
there a way to have it just selectively choose which pages it will print off
that sheet?

"Dave Peterson" wrote:

If you can build an array of those sheetnames, you can print that as a group.

Try selecting a few sheets (click on the first and ctrl-click on subsequent).

Then print that test.

If that prints as one job, maybe you build that array of names in code:

Option Explicit
Sub testme()

Dim mySheetNames() As String
Dim iCtr As Long
Dim wCtr As Long

iCtr = 0
For wCtr = 1 To Sheets.Count
If wCtr Mod 2 = 0 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Sheets(wCtr).Name
End If
Next wCtr

If iCtr = 0 Then
'do nothing
Else
Sheets(mySheetNames).PrintOut preview:=True
End If

End Sub

This example just looked for even numbered worksheets. But you could loop
through your checkboxes and build that array.





SueDot wrote:

Hi -
I have multiple workbooks containing 60 printable graph pages each. In the
front of each workbook I have a "table of contents" worksheet where a user
can click a check box for whichever of the 60 graphs they need to print at a
given time. This works great except that the way I've written the macro,
each selection goes to the printer individually. Is there a way to group the
numerous selections a user may have checked and send them to the printer as
just one print job? Here's an example of the working macro as currently
written--

Print Graphs Macro:

Dim Copies As Integer

Dim TOC As String, R12G As String, PVCG As String, M36G As String
TOC = "Table of Contents"
R12G = "Rolling 12 Graphs"
PVCG = "Premium_vs_Claims_Graphs"
M36G = "Monthly_36_Graphs"

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

Application.ScreenUpdating = False

With Sheets(TOC)
Copies = .CopyCount.Value
If .P1RT.Value = True Then
Sheets(R12G).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If
If .P1PC.Value = True Then
Sheets(PVCG).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=Copies, Collate:=True
End If

Note: The "P1RT" and "P1PC" represent names given to the check boxes. I
have 60 checkboxes and am only showing the commands for two of them here, to
save space.

Thanks in advance for any ideas!
Susan

--

Dave Peterson



--

Dave Peterson