copying and pasting loop

  • Thread starter Thread starter Kenneth Smith
  • Start date Start date
K

Kenneth Smith

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
 
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
 
Back
Top