populate output form programmatically
 

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

populate output form programmatically
 

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

"access user" wrote:

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

populate output form programmatically
 

Thanks Joel - I'll give it a go and let you know how I get on.
James

"Joel" wrote:


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

"access user" wrote:

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

populate output form programmatically
 

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


"access user" wrote:

Thanks Joel - I'll give it a go and let you know how I get on.
James

"Joel" wrote:


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

"access user" wrote:

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