Answered by:
Create and populate multiple copies of form in Excel
Question

Dear Forum,
I need to create several copies of a printable form in Excel, with data from a list populating two of the cells on each form. For the purposes of this discussion, let us set the following parameters. The data are in two columns, "Number" and "Name," on a sheet called, "Data." There is a Form on a sheet called, "Form." The form has 40 rows and 10 columns. I want to put the data from the first row of Data in the "Number" column in cell B2 of the Form, and the data from the "Name" column in Cell D2. For each row of data in the Data sheet, I want to create a new copy of the Form (on the same tab), with data from the corresponding row in the proper cells. Each form should print as a single page. So if I have 10 rows of data on the Data Sheet, I should get 10 copies of the Form.
I would expect the code would do the following steps.
1. Count the number of rows of data ("PageCount")
2. Copy the form "PageCount" number of times
2a. insert page breaks
3. Copy and paste the data in the rows into the proper cells
Thank you in advance for your help.
Answers

@barence773
When you talk form, I'm assuming that it is not a UserForm, right?
There's an example workbook ready to download at: http://www.ambienteoffice.com.br/excel/gerar_planilhas_com_base_em_uma_planilha_padrao_e_uma_tabela/exemplo.rar
Actually, this is the explanation of the page I wrote at: http://www.ambienteoffice.com.br/excel/gerar_planilhas_com_base_em_uma_planilha_padrao_e_uma_tabela/
The page is in portuguese, so you might want to translate it.
Felipe Costa Gualberto  http://www.ambienteoffice.com.br
 Marked as answer by barence773 Thursday, October 18, 2012 7:54 PM
All replies

Let us analyze this
1  OK The number of row is easy enough. To find the last row 
Dim xlBook as Workbook
Dim iRows as Integer
Set xlBook = ActiveWorkbook
iRows = xlbook.Sheets("Data").Range("A" & xlbook.Sheets("Data").Rows.Count).End(4162).Row
and subtract 1 if there is a header row
2 and 3. Why? If the aim is to print the forms, why not simply update the same Form sheet and print it as many times as required?
Graham Mayor  Word MVP
www.gmayor.com

@barence773
When you talk form, I'm assuming that it is not a UserForm, right?
There's an example workbook ready to download at: http://www.ambienteoffice.com.br/excel/gerar_planilhas_com_base_em_uma_planilha_padrao_e_uma_tabela/exemplo.rar
Actually, this is the explanation of the page I wrote at: http://www.ambienteoffice.com.br/excel/gerar_planilhas_com_base_em_uma_planilha_padrao_e_uma_tabela/
The page is in portuguese, so you might want to translate it.
Felipe Costa Gualberto  http://www.ambienteoffice.com.br
 Marked as answer by barence773 Thursday, October 18, 2012 7:54 PM

The reason for points 2 and 3 is that I need to be able to save the printable forms in a single file. In my job, I fill in some of the information in the forms, then send them to another group, where the rest of the data is entered. Finally, the completed forms are returned to my group, and we print them out.
