Loop & Print
 

Hi,
I have a workbook with a sheet (“Data Sheet”) that contains
approximately 50,000 rows of data and extends out to column AC. Each
row contains a unique data record, but many of the columns on each row
are blank. A second sheet (“Summary”) contains a user form with five
list boxes where the User can select criteria (Supervisor, Worker,
Work Area, Work Location, and Job Number) to search data sheet. After
the User selects the search criteria, the data is manipulated and then
presented in a pre-formatted section of the summary page, only one
record at a time, and then printed out. My question is, how do I get
the pre-formatted section of the summary page to print for each record
without any User intervention? So, if I find that a supervisor has 21
workers that worked on a specific job number, how can I get each
individual record to print consecutively? I have no idea where to
start on this one. Thanks.
James

Loop & Print
 

What code do you have so far?

Tim


On Jan 11, 5:17*pm, John Smith wrote:
Hi,
I have a workbook with a sheet (“Data Sheet”) that contains
approximately 50,000 rows of data and extends out to column AC. Each
row contains a unique data record, but many of the columns on each row
are blank. A second sheet (“Summary”) contains a user form with five
list boxes where the User can select criteria (Supervisor, Worker,
Work Area, Work Location, and Job Number) to search data sheet. After
the User selects the search criteria, the data is manipulated and then
presented in a pre-formatted section of the summary page, only one
record at a time, and then printed out. *My question is, how do I get
the pre-formatted section of the summary page to print for each record
without any User intervention? So, if I find that a supervisor has 21
workers that worked on a specific job number, how can I get each
individual record to print consecutively? I have no idea where to
start on this one. Thanks.
James

Loop & Print
 

On Jan 11, 7:46*pm, Tim Williams wrote:
What code do you have so far?

Tim

On Jan 11, 5:17*pm, John Smith wrote:



Hi,
I have a workbook with a sheet (“Data Sheet”) that contains
approximately 50,000 rows of data and extends out to column AC. Each
row contains a unique data record, but many of the columns on each row
are blank. A second sheet (“Summary”) contains a user form with five
list boxes where the User can select criteria (Supervisor, Worker,
Work Area, Work Location, and Job Number) to search data sheet. After
the User selects the search criteria, the data is manipulated and then
presented in a pre-formatted section of the summary page, only one
record at a time, and then printed out. *My question is, how do I get
the pre-formatted section of the summary page to print for each record
without any User intervention? So, if I find that a supervisor has 21
workers that worked on a specific job number, how can I get each
individual record to print consecutively? I have no idea where to
start on this one. Thanks.
James- Hide quoted text -

- Show quoted text -

Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.

Loop & Print
 

hi James,

can you show us the macro who doing realise this part:

"After the User selects the search criteria, the data is manipulated
and then presented in a pre-formatted section of the summary page,
only one record at a time, and then printed out"


--
isabelle


Le 2012-01-11 20:57, John Smith a écrit :

Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.

Loop & Print
 

or what is the manipulation (or method you used) to doing the presentation in the summary page ?

--
isabelle


Le 2012-01-11 22:10, isabelle a écrit :
hi James,

can you show us the macro who doing realise this part:

"After the User selects the search criteria, the data is manipulated
and then presented in a pre-formatted section of the summary page,
only one record at a time, and then printed out"

Loop & Print
 

On Jan 11, 9:10*pm, isabelle wrote:
hi James,

can you show us the macro who doing realise this part:

"After the User selects the search criteria, the data is manipulated
and then presented in a pre-formatted section of the summary page,
only one record at a time, and then printed out"

--
isabelle

Le 2012-01-11 20:57, John Smith a écrit :



Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.- Hide quoted text -

- Show quoted text -

The data is compiled using cell formulas and then stored in a range on
the worksheeet. The userform simply requests what record data needs to
be displayed on the summary page. If the request returns 9 records
then I need to fill the pre-formatted section of the summary page with
the first record, print it, and then repeat the process for the
remaing 8 records. I need to figure out how to automate the loop
through the print cycle.

Loop & Print
 

ok, you can add a CommandButton with this code,

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
Sheets("summary").ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub


--
isabelle




Le 2012-01-11 22:20, John Smith a écrit :

The data is compiled using cell formulas and then stored in a range on
the worksheeet. The userform simply requests what record data needs to
be displayed on the summary page. If the request returns 9 records
then I need to fill the pre-formatted section of the summary page with
the first record, print it, and then repeat the process for the
remaing 8 records. I need to figure out how to automate the loop
through the print cycle.

Loop & Print
 

On Jan 11, 9:48*pm, isabelle wrote:
ok, you can add a CommandButton with this code,

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
* * *Sheets("summary").ListBox1.ListIndex = i
* * *Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

--
isabelle

Le 2012-01-11 22:20, John Smith a crit :



The data is compiled using cell formulas and then stored in a range on
the worksheeet. The userform simply requests what record data needs to
be displayed on the summary page. If the request returns 9 records
then I need to fill the pre-formatted section of the summary page with
the first record, print it, and then repeat the process for the
remaing 8 records. I need to figure out how to automate the loop
through the print cycle.- Hide quoted text -

- Show quoted text -

Thanks, Isabelle, but I keep getting a run-time error 438 on this
block of code.

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1 == ERROR 438 HERE
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
Sheets("summary").ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

Loop & Print
 

You don't have any code at all, or just no code for the printing?

Tim


On Jan 11, 5:57*pm, John Smith wrote:
On Jan 11, 7:46*pm, Tim Williams wrote:

What code do you have so far?

Tim


- Show quoted text -

Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.- Hide quoted text -

- Show quoted text -

Loop & Print
 

On Jan 12, 11:36*am, Tim Williams wrote:
You don't have any code at all, or just no code for the printing?

Tim

On Jan 11, 5:57*pm, John Smith wrote:



On Jan 11, 7:46*pm, Tim Williams wrote:
What code do you have so far?

Tim

- Show quoted text -

Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I don't have the print routine, but the part that loads the supervisor
is:

Private Sub UserForm_Initialize()
Dim R As Long
For R = 2 To Sheets("Data Sheet").Cells(65000, 1).End(xlUp).Row
If Sheets("Data Sheet").Cells(R, 1).Value < " " Then
ListBox1.AddItem Sheets("Data Sheet").Cells(R, 1).Value
End If
Next

Loop & Print
 

What are the names of the "listbox" on Summary sheet ?

--
isabelle



Le 2012-01-12 10:33, John Smith a écrit :

Thanks, Isabelle, but I keep getting a run-time error 438 on this
block of code.

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1 == ERROR 438 HERE
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
Sheets("summary").ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

Loop & Print
 

On Jan 12, 2:14*pm, isabelle wrote:
What are the names of the "listbox" on Summary sheet ?

--
isabelle

Le 2012-01-12 10:33, John Smith a écrit :



Thanks, Isabelle, but I keep getting a run-time error 438 on this
block of code.

* Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1 == *ERROR 438 HERE
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
* * * Sheets("summary").ListBox1.ListIndex = i
* * * Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub- Hide quoted text -

- Show quoted text -

I don't have them named, I was planning on passing the value directly
to the cell. Do the cells have to be named ranges?

Loop & Print
 

James, do a right click to the listbox and a click on view code and tell me what you see

--
isabelle

Le 2012-01-12 15:24, John Smith a écrit :

I don't have them named, I was planning on passing the value directly
to the cell. Do the cells have to be named ranges?

Loop & Print
 

On Jan 12, 3:13*pm, isabelle wrote:
James, do a right click to the listbox and a click on view code and tell me what you see

--
isabelle

Le 2012-01-12 15:24, John Smith a écrit :



I don't have them named, I was planning on passing the value directly
to the cell. Do the cells have to be named ranges?- Hide quoted text -

- Show quoted text -

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Student Profile").ListBox1.ListIndex = -1 'Error 438
For i = 0 To Sheets("Student Profile").ListBox1.ListCount - 1
Sheets("Student Profile").ListBox1.ListIndex = i
Sheets("Student Profile").PrintPreview 'test with PrintPreview
then change for PrintOut for a real print
Next

End Sub
Private Sub UserForm_Initialize()
Dim R As Long
For R = 2 To Sheets("DataLookupSheet").Cells(65000, 1).End(xlUp).Row
If Sheets("DataLookupSheet").Cells(R, 1).Value < " " Then
ListBox1.AddItem Sheets("DataLookupSheet").Cells(R, 1).Value
End If
Next
End Sub

Loop & Print
 

ok, the listbox are not on a sheet but on a userform.
place the commandbutton on the userform
and change the code by this one

Private Sub CommandButton1_Click()
Dim i As Integer
UserForm1.ListBox1.ListIndex = -1
For i = 0 To UserForm1.ListBox1.ListCount - 1
UserForm1.ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub



--
isabelle

Loop & Print
 

On Jan 12, 4:31*pm, isabelle wrote:
ok, the listbox are not on a sheet but on a userform.
place the commandbutton on the userform
and change the code by this one

Private Sub CommandButton1_Click()
Dim i As Integer
UserForm1.ListBox1.ListIndex = -1
For i = 0 To UserForm1.ListBox1.ListCount - 1
* * *UserForm1.ListBox1.ListIndex = i
* * *Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

--
isabelle

Thanks, Isabelle. It seemed to work, but I couldn't get out of Excel -
I couldn't use the 'Esc' key or 'X' out of it, I had to use Task
Manager to kill Excel. I then got a message box that popped up and
said,"Automation error" "Catastrophic error". When I clicked on the
"OK" button, it gave me an "Out of memory" error. Any idea as to what
caused it and how to fix it?
Then I was curious about putting the controls directly on the sheet
instead of the user form. Is there a preferred method or are they both
about the same? Thanks.
James

Loop & Print
 

James, can you put your file on cjoint.com or other

--
isabelle


Le 2012-01-13 09:08, John Smith a écrit :


Thanks, Isabelle. It seemed to work, but I couldn't get out of Excel -
I couldn't use the 'Esc' key or 'X' out of it, I had to use Task
Manager to kill Excel. I then got a message box that popped up and
said,"Automation error" "Catastrophic error". When I clicked on the
"OK" button, it gave me an "Out of memory" error. Any idea as to what
caused it and how to fix it?
Then I was curious about putting the controls directly on the sheet
instead of the user form. Is there a preferred method or are they both
about the same? Thanks.
James

Loop & Print
 

On Jan 13, 8:37*am, isabelle wrote:
James, can you put your file on cjoint.com or other

--
isabelle

Le 2012-01-13 09:08, John Smith a écrit :





Thanks, Isabelle. It seemed to work, but I couldn't get out of Excel -
I couldn't use the 'Esc' key or 'X' out of it, I had to use Task
Manager to kill Excel. I then got a message box that popped up and
said,"Automation error" "Catastrophic error". When I clicked on the
"OK" button, it gave me an "Out of memory" error. Any idea as to what
caused it and how to fix it?
Then I was curious about putting the controls directly on the sheet
instead of the user form. Is there a preferred method or are they both
about the same? Thanks.
James- Hide quoted text -

- Show quoted text -

Sorry, Isabelle, but I can't get permission to post it, but I do
believe I have solved that problem (I had some bad code in another
module). After giving this a little more thought, I think I will
gather the information from the user form, run it against an advanced
filter on another sheet, then populate the summary sheet. Does that
sound like a reasonable way of doing it or can you think of an easier
way.