Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing a form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing a form
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:D 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 -- - K Dales "Cheryl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop form printing | Excel Worksheet Functions | |||
stop form printing | Excel Discussion (Misc queries) | |||
printing onto re printed form | Excel Discussion (Misc queries) | |||
I created a form on excel. want to edit the form without printing | Excel Discussion (Misc queries) | |||
Printing A Form | Excel Programming |