Printing a form

[Guest]

I have 2 pages in Excel sheet.

Sheet 1 contains database with information like voucher no. and name.
Sheet 2 is the form with blank fields.
The blank fields will pull the voucher no and name from sheet 1.

I would like the form (sheet 2) to pull the data in sheet 1 line after line
after printing.
Eg. after printing the form using line 1 of the data in sheet 1, it will
automatically pull line 2 data in sheet 1 and print the form.
after printing the form using line 2 of the data in sheet 1, it will
automatically pull line 3 data in sheet 1 and print the form
This continues till there is no more data in sheet 1.

Thank you

 

[Guest]

Hard to give a good answer without knowing more about the form setup, but
here are some ideas:
For my example, I will say you use these columns of data in columns A of
Sheet1:
DATE VOUCHER# COST NAME
Somehow the cells that act as data fields in your form need to point to
columns in your data list, and then there needs to be a way of changing rows.
I will usually store the row number in a cell somewhere, hidden if
necessary. Let's say I have that incell A1 of Sheet2. And I want cell B2
to show the voucher # for whatever row I have selected. The formula in B2
could be:
=OFFSET(Sheet1!$A$1,A1,1)
(note: this assumes I have a header row on Sheet1 and the value in A1 uses 1
for the first row of data (i.e. row#2), not row 1 on the sheet)
Using similar formulas you can fill in the rest of your form. So now to
print all the forms, you just need a macro that keeps adding 1 to the row
number in cell A1, prints the sheet, and repeats until you are at the end of
your rows. Here is a macro that will do that, assuming again there is a
header row on sheet1 and also that there are no blank lines in the list:

Sub PrintForm()
Dim ThisRow As Range
With Worksheets("Sheet1").Range("A1").CurrentRegion
For Each ThisRow In .Rows
Worksheets("Sheet2").Range("A1").Value = ThisRow.Row
If Worksheets("Sheet2").Range("A1").Value <> .Rows.Count _
Then Worksheets("Sheet2").PrintOut
Next ThisRow
End With
End Sub