+ Reply to Thread
Results 1 to 6 of 6

worksheet grouping, Sheets(Array... property

  1. #1
    rmblazei@hotmail.com
    Guest

    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


  2. #2
    Nick Hodge
    Guest

    Re: worksheet grouping, Sheets(Array... property

    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
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    <rmblazei@hotmail.com> wrote in message
    news:1152739357.449963.322860@s13g2000cwa.googlegroups.com...
    >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
    >




  3. #3
    rmblazei@hotmail.com
    Guest

    Re: worksheet grouping, Sheets(Array... property

    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
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > <rmblazei@hotmail.com> wrote in message
    > news:1152739357.449963.322860@s13g2000cwa.googlegroups.com...
    > >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
    > >



  4. #4
    Nick Hodge
    Guest

    Re: worksheet grouping, Sheets(Array... property

    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
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    <rmblazei@hotmail.com> wrote in message
    news:1152741048.875198.153040@s13g2000cwa.googlegroups.com...
    > 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
    >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >>
    >>
    >> <rmblazei@hotmail.com> wrote in message
    >> news:1152739357.449963.322860@s13g2000cwa.googlegroups.com...
    >> >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
    >> >

    >




  5. #5
    Dave Peterson
    Guest

    Re: worksheet grouping, Sheets(Array... property

    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


    rmblazei@hotmail.com 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

  6. #6
    rmblazei@hotmail.com
    Guest

    Re: worksheet grouping, Sheets(Array... property

    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
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > <rmblazei@hotmail.com> wrote in message
    > news:1152741048.875198.153040@s13g2000cwa.googlegroups.com...
    > > 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
    > >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    > >>
    > >>
    > >> <rmblazei@hotmail.com> wrote in message
    > >> news:1152739357.449963.322860@s13g2000cwa.googlegroups.com...
    > >> >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
    > >> >

    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1