Printing a workbook one row at a time filling in a form with the d

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a workbook with about 20,000 rows of data. I want to design a form
and have the information from one row inserted into the form and be printed
out. The form would also have empty places for the sales people to write
additional information on the paper after they make the sales call.

The information is name, phone number, address and several other customer
related information cells. I am using Excell 2000.

IF possible I would like to print these 2 up on a standard piece of paper
8.5 by 11 inches. This means I would print two forms on one page with a
seperate record/row filling in each form. I hope that makes sence.

Thank you for your time and help with this. I am totally lost. Have a good day
 
Insert a new worksheet as your form (and name it, say, "From"), and design
it so that there are two copies of the form (which print out the way that
you like).

To pull data into the form, use a key value cell (which need not be part of
the print area). The key value can be as simple as the row number, and
formulas that reference that key value can be used to pull data into the
forms.

For example, put the number 2 into cell A1. In the cells of your first
form, use formulas like

=INDIRECT("'Data Sheet Name'!A" & A1)
=INDIRECT("'Data Sheet Name'!B" & A1)
=INDIRECT("'Data Sheet Name'!C" & A1)
=INDIRECT("'Data Sheet Name'!D" & A1)
=INDIRECT("'Data Sheet Name'!E" & A1)

To pull data from columns A to E, row 2.

And in the second copy of the form, use formulas like

=INDIRECT("'Data Sheet Name'!A" & A1+1)
=INDIRECT("'Data Sheet Name'!B" & A1+1)
=INDIRECT("'Data Sheet Name'!C" & A1+1)
=INDIRECT("'Data Sheet Name'!D" & A1+1)
=INDIRECT("'Data Sheet Name'!E" & A1+1)

To pull data from columns A to E, row 3.

Then when you enter a value into cell A1, it will grab data from 2 rows on
your data sheet, and the form can be printed.

If you want to print all your data, you could use a simple macro to
increment the value in cell A1 (in steps of 2), recalc, and print, along the
lines of

Dim i As Integer
For i = 1 To 20000 Step 2
Worksheets("Form").Range("A1").Value = i
Application.CalculateFull
Worksheets("Form").Printout
Next i

Just make sure you have a lot of paper and toner available.

HTH,
Bernie
MS Excel MVP
 
Back
Top