Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 HIS wrote in message oups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 HIS wrote in message oups.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 HIS wrote in message oups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 HIS wrote in message oups.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 HIS wrote in message oups.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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping sheets copies Header across to all sheets | Excel Worksheet Functions | |||
Worksheet Sort - but all sheets in array do not exist | Excel Discussion (Misc queries) | |||
Grouping Sheets | Excel Discussion (Misc queries) | |||
grouping sheets | Excel Programming | |||
Grouping Sheets in VBA | Excel Programming |