Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box to populate variable output range | Excel Discussion (Misc queries) | |||
Is it possible to change the Input Range of a Form Control programmatically? | Excel Programming | |||
how to create a user form programmatically | Excel Programming | |||
Excel VBA Programmatically delete a form? | Excel Programming | |||
Form output naming | Excel Programming |