copying and pasting loop
 

Hi,

I am in great need of a macro that can do the following:

In Sheet1, columns A, B, and C there are three arrays of
variables. I need to copy, line by line, a variable from
each array and paste (as values) these three variables
into 3 different cells in Sheet2 (let's say B3, B10, B12).
I would prefer if the macro copied and pasted the 3
variables independently of each other rather simply
selecting the range (A1:C1). The input into the three
cells in Sheet2 will change the output in cells C12, C15,
and C20 (also in Sheet2). I then want to copy this output
and record it (i.e. paste it) in columns D, E, and F in
Sheet1 (again preferably independently each other rather
than selecting a range). I need the procedure above
performed line by line as the arrays range from cell row 1
to 20. That is, I need to procedure performed for (Sheet1)
row 1, then row 2, and so forth. Any ideas of how to do
this? Your help is greatly appreciated.

Kenneth Smith

copying and pasting loop
 

Kenneth

Try this

Sub test()

Dim cell As Range
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")

For Each cell In sh1.Range("A1:A20")
sh2.Range("b3").Value = cell.Value
sh2.Range("b10").Value = cell.Offset(0, 1).Value
sh2.Range("b12").Value = cell.Offset(0, 2).Value
cell.Offset(0, 3).Value = sh2.Range("c12").Value
cell.Offset(0, 4).Value = sh2.Range("c15").Value
cell.Offset(0, 5).Value = sh2.Range("C20").Value
Next cell

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Kenneth Smith" wrote in message
...
Hi,

I am in great need of a macro that can do the following:

In Sheet1, columns A, B, and C there are three arrays of
variables. I need to copy, line by line, a variable from
each array and paste (as values) these three variables
into 3 different cells in Sheet2 (let's say B3, B10, B12).
I would prefer if the macro copied and pasted the 3
variables independently of each other rather simply
selecting the range (A1:C1). The input into the three
cells in Sheet2 will change the output in cells C12, C15,
and C20 (also in Sheet2). I then want to copy this output
and record it (i.e. paste it) in columns D, E, and F in
Sheet1 (again preferably independently each other rather
than selecting a range). I need the procedure above
performed line by line as the arrays range from cell row 1
to 20. That is, I need to procedure performed for (Sheet1)
row 1, then row 2, and so forth. Any ideas of how to do
this? Your help is greatly appreciated.

Kenneth Smith