Loop & Print

J

John Smith

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
 
J

John Smith

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.
 
I

isabelle

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"
 
I

isabelle

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

John Smith

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 :




- 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.
 
I

isabelle

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 :
 
J

John Smith

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 :




- Show quoted text -

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

Private Sub CommandButton1_Click()
 
J

John Smith

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

Tim





- 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
 
I

isabelle

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

--
isabelle



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

John Smith

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

--
isabelle

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





- 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?
 
I

isabelle

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

John Smith

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 :




- 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
 
I

isabelle

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
 
J

John Smith

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

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
 
J

John Smith

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

--
isabelle

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






- 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top