worksheet grouping, Sheets(Array... property

[rmblazei]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[rmblazei]

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]

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
(e-mail address removed)

 

[Dave Peterson]

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