Problems with copying and pasting range

[Guest]

Hi,

I have developed the macro below to take a number from a dataset (in
"Sheet1"), copy and paste it into a model (in "Sheet2") and then copy an
paste the model output back into a different column in the original dataset
(in "Sheet1").

Sub Macro()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("Range1")
cell.Resize(1, 1).Copy

With Worksheets("Sheet1")
.Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
cell.Offset(0, 9).Value = .Range("Cell2").Value
End With
Next
End Sub


Here is my issue, rather than simply pasting one value from "Cell2" back
into "Sheet1", I would like to paste a "Range2" back into "Sheet1",
offsetting this value [0,9] from "Range1". However, when I put in a range
rather than a cell-reference in "Cell2", the macro stops working. Does anyone
have any suggestions as to what I am doing wrong?

In advance, thank you very much!

 

[Tom Ogilvy]

assuming Range2 is a 1 row by several columns wide range of contiguous
cells:

Sub Macro()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("Range1")
cell.Resize(1, 1).Copy

With Worksheets("Sheet1")
.Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
numcol = .Range("Range2").Columns.count
cell.Offset(0, 9).Resize(1,numcol).Value = .Range("Range2").Value
End With
Next
End Sub