populate output form programmatically

  • Thread starter Thread starter access user
  • Start date Start date
A

access user

Hi

On one sheet I have rows of data (sheet1).

On another I have a form template (sheet2).

Each row on sheet1 constitutes one form's worth of data. I need to automate
a process to go down one row at a time, populate the form and print it. Next
row, populate form and print it. Then stop after the last row of data on
sheet1.

tia
James
 
with sheets(Sheet1")
Sh1RowCount = 1
do while .Range("A" & Sh1RowCount) <> ""
'get data from sheet 1
Item1 = .Range("A" & Sh1RowCount)
Item2 = .Range("B" & Sh1RowCount)
Item3 = .Range("C" & Sh1RowCount)
'copy templet to new worksheet
sheets("Sheet2").copy after:=sheets(sheets.count)
newsht = Activesheet
with newsht
.Range("A1") = Item1
.Range("B2") = Item2
.Range("C3") = Item3
end with
Sh1RowCount = Sh1RowCount + 1
loop
end with
 
You can perform the copies in one instruction instead of two. My original
code I did it in two instructions so you wuld get the concept. Here is code
to perform the copies in one instruction

with sheets(Sheet1")
Sh1RowCount = 1
do while .Range("A" & Sh1RowCount) <> ""

'copy templet to new worksheet
sheets("Sheet2").copy after:=sheets(sheets.count)
newsht = Activesheet

newsht.Range("A1") = .Range("A" & Sh1RowCount)
newsht.Range("B2") = .Range("B" & Sh1RowCount)
newsht.Range("C3") = .Range("C" & Sh1RowCount)

Sh1RowCount = Sh1RowCount + 1
loop
end with
 
Back
Top