worksheet grouping, Sheets(Array... property

I have the following bit of code that J-walk.com graciously makes
available on its batch printer tool:

' Begin routine
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
With Sheets(ListBox1.List(i))
.PrintOut Copies:=NumberCopy.Text, Collate:=True
End With
End If
Next i

This works great if you want individual printouts, but I would like to
modify it to group the selected worksheets and then print as one job
(so that auto page numbers will be sequential). I believe I need to
use the Sheets(Array(... property with the .Select property prior to
the .PrintOut property, but I cannot figure out how to arrange it after
many attempts. I am very much a newbie when it comes to VBA. Any help
is appreciated and thank you in advance.

xl2003
winxppro

How about

Sub GroupAndPrint()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut
End Sub


You can set parameters for the printout, like number of copies, start page,
printer, etc if you need
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
>I have the following bit of code that J-walk.com graciously makes
> available on its batch printer tool:
>
> ' Begin routine
> For i = 0 To ListBox1.ListCount - 1
> If ListBox1.Selected(i) Then
> With Sheets(ListBox1.List(i))
> .PrintOut Copies:=NumberCopy.Text, Collate:=True
> End With
> End If
> Next i
>
> This works great if you want individual printouts, but I would like to
> modify it to group the selected worksheets and then print as one job
> (so that auto page numbers will be sequential). I believe I need to
> use the Sheets(Array(... property with the .Select property prior to
> the .PrintOut property, but I cannot figure out how to arrange it after
> many attempts. I am very much a newbie when it comes to VBA. Any help
> is appreciated and thank you in advance.
>
> xl2003
> winxppro
>

Thanks for the reply Nick. this would work if the sheet names being
printed were always the same, but this bit of code is from a user form
that pops up and lists all of the sheets in a workbook (this particular
one has about 50). I have it saved in my personal workbook so I can
use it in any workbook that is open. On the user form you can select
which sheets you want to print in the list box and choose how many
copies you would like, collate yes/no, etc. What I would like to do is
change the code from printing each selected sheet in the list box as
its own job, to grouping all of the selected worksheets selected (will
vary each time depending on the user) and printing as one grouped job.
Perhaps it is not possible or I am going about it the wrong way. The
code I posted below is the routine that runs when you click the OK
button on the form. Would posting all of the code help? It is a
userform available gratis from J-Walk.com that has been only slightly
modified.

Thanks again


Nick Hodge wrote:
> How about
>
> Sub GroupAndPrint()
> Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut
> End Sub
>
>
> You can set parameters for the printout, like number of copies, start page,
> printer, etc if you need
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >I have the following bit of code that J-walk.com graciously makes
> > available on its batch printer tool:
> >
> > ' Begin routine
> > For i = 0 To ListBox1.ListCount - 1
> > If ListBox1.Selected(i) Then
> > With Sheets(ListBox1.List(i))
> > .PrintOut Copies:=NumberCopy.Text, Collate:=True
> > End With
> > End If
> > Next i
> >
> > This works great if you want individual printouts, but I would like to
> > modify it to group the selected worksheets and then print as one job
> > (so that auto page numbers will be sequential). I believe I need to
> > use the Sheets(Array(... property with the .Select property prior to
> > the .PrintOut property, but I cannot figure out how to arrange it after
> > many attempts. I am very much a newbie when it comes to VBA. Any help
> > is appreciated and thank you in advance.
> >
> > xl2003
> > winxppro
> >

Post all the code, but the theory would be to assign the items selected in
the listbox to an array and then use that to group the sheets and then
assign the other options to variables and apply them at the end of the line
I have given you

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
> Thanks for the reply Nick. this would work if the sheet names being
> printed were always the same, but this bit of code is from a user form
> that pops up and lists all of the sheets in a workbook (this particular
> one has about 50). I have it saved in my personal workbook so I can
> use it in any workbook that is open. On the user form you can select
> which sheets you want to print in the list box and choose how many
> copies you would like, collate yes/no, etc. What I would like to do is
> change the code from printing each selected sheet in the list box as
> its own job, to grouping all of the selected worksheets selected (will
> vary each time depending on the user) and printing as one grouped job.
> Perhaps it is not possible or I am going about it the wrong way. The
> code I posted below is the routine that runs when you click the OK
> button on the form. Would posting all of the code help? It is a
> userform available gratis from J-Walk.com that has been only slightly
> modified.
>
> Thanks again
>
>
> Nick Hodge wrote:
>> How about
>>
>> Sub GroupAndPrint()
>> Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut
>> End Sub
>>
>>
>> You can set parameters for the printout, like number of copies, start
>> page,
>> printer, etc if you need
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> www.nickhodge.co.uk
>> [email protected]HIS
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> >I have the following bit of code that J-walk.com graciously makes
>> > available on its batch printer tool:
>> >
>> > ' Begin routine
>> > For i = 0 To ListBox1.ListCount - 1
>> > If ListBox1.Selected(i) Then
>> > With Sheets(ListBox1.List(i))
>> > .PrintOut Copies:=NumberCopy.Text, Collate:=True
>> > End With
>> > End If
>> > Next i
>> >
>> > This works great if you want individual printouts, but I would like to
>> > modify it to group the selected worksheets and then print as one job
>> > (so that auto page numbers will be sequential). I believe I need to
>> > use the Sheets(Array(... property with the .Select property prior to
>> > the .PrintOut property, but I cannot figure out how to arrange it after
>> > many attempts. I am very much a newbie when it comes to VBA. Any help
>> > is appreciated and thank you in advance.
>> >
>> > xl2003
>> > winxppro
>> >
>

Maybe something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim myArr() As String
Dim SelectedCount As Long

'an array that will hold all the sheet names
ReDim myArr(1 To Me.ListBox1.ListCount)

SelectedCount = 0
For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) Then
SelectedCount = SelectedCount + 1
myArr(SelectedCount) = Me.ListBox1.List(iCtr)
End If
Next iCtr

If SelectedCount = 0 Then
'do nothing
Else
'just use the part that we used--kill the rest
ReDim Preserve myArr(1 To SelectedCount)
Me.Hide 'for preview:=true
Sheets(myArr).PrintOut preview:=True
'commented out
'Copies:=NumberCopy.Text, Collate:=True
Me.Show
End If

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim sht As Object
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For Each sht In ActiveWorkbook.Sheets
.AddItem sht.Name
Next sht
End With

End Sub


[email protected] wrote:
>
> I have the following bit of code that J-walk.com graciously makes
> available on its batch printer tool:
>
> ' Begin routine
> For i = 0 To ListBox1.ListCount - 1
> If ListBox1.Selected(i) Then
> With Sheets(ListBox1.List(i))
> .PrintOut Copies:=NumberCopy.Text, Collate:=True
> End With
> End If
> Next i
>
> This works great if you want individual printouts, but I would like to
> modify it to group the selected worksheets and then print as one job
> (so that auto page numbers will be sequential). I believe I need to
> use the Sheets(Array(... property with the .Select property prior to
> the .PrintOut property, but I cannot figure out how to arrange it after
> many attempts. I am very much a newbie when it comes to VBA. Any help
> is appreciated and thank you in advance.
>
> xl2003
> winxppro

--

Dave Peterson

Here is the code for the OK button:
========
Private Sub OKButton_Click()
' This command will print all selected sheets in the active workbook

' Make sure a number of copies is entered
If NumberCopy.Text = "" Then
MsgBox "You must enter number of copies desired."
Exit Sub
End If
' Begin routine
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
With Sheets(ListBox1.List(i))
.PrintOut Copies:=NumberCopy.Text, Collate:=True
End With
End If
Next i
Unload Me
End Sub
========

I am going to have a run at Dave's suggestion. I am not very familiar
with VBA so it may take a bit. I guess my hope of sneaking a group
command in the existing code is not possible.

Thanks again both of you.


Nick Hodge wrote:
> Post all the code, but the theory would be to assign the items selected in
> the listbox to an array and then use that to group the sheets and then
> assign the other options to variables and apply them at the end of the line
> I have given you
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the reply Nick. this would work if the sheet names being
> > printed were always the same, but this bit of code is from a user form
> > that pops up and lists all of the sheets in a workbook (this particular
> > one has about 50). I have it saved in my personal workbook so I can
> > use it in any workbook that is open. On the user form you can select
> > which sheets you want to print in the list box and choose how many
> > copies you would like, collate yes/no, etc. What I would like to do is
> > change the code from printing each selected sheet in the list box as
> > its own job, to grouping all of the selected worksheets selected (will
> > vary each time depending on the user) and printing as one grouped job.
> > Perhaps it is not possible or I am going about it the wrong way. The
> > code I posted below is the routine that runs when you click the OK
> > button on the form. Would posting all of the code help? It is a
> > userform available gratis from J-Walk.com that has been only slightly
> > modified.
> >
> > Thanks again
> >
> >
> > Nick Hodge wrote:
> >> How about
> >>
> >> Sub GroupAndPrint()
> >> Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut
> >> End Sub
> >>
> >>
> >> You can set parameters for the printout, like number of copies, start
> >> page,
> >> printer, etc if you need
> >> --
> >> HTH
> >> Nick Hodge
> >> Microsoft MVP - Excel
> >> Southampton, England
> >> www.nickhodge.co.uk
> >> [email protected]HIS
> >>
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have the following bit of code that J-walk.com graciously makes
> >> > available on its batch printer tool:
> >> >
> >> > ' Begin routine
> >> > For i = 0 To ListBox1.ListCount - 1
> >> > If ListBox1.Selected(i) Then
> >> > With Sheets(ListBox1.List(i))
> >> > .PrintOut Copies:=NumberCopy.Text, Collate:=True
> >> > End With
> >> > End If
> >> > Next i
> >> >
> >> > This works great if you want individual printouts, but I would like to
> >> > modify it to group the selected worksheets and then print as one job
> >> > (so that auto page numbers will be sequential). I believe I need to
> >> > use the Sheets(Array(... property with the .Select property prior to
> >> > the .PrintOut property, but I cannot figure out how to arrange it after
> >> > many attempts. I am very much a newbie when it comes to VBA. Any help
> >> > is appreciated and thank you in advance.
> >> >
> >> > xl2003
> >> > winxppro
> >> >
> >