copying and pasting range problem

[Guest]

Hi,

This is a continuation to a previous thread.

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 and
paste the model output back over a different range of columns 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("Sheet2")
.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 transposed a "Range2" of 70 values back
into "Sheet1", offsetting this value [0,9] from "Range1". I tried the
following solution:

With Worksheets("Sheet2")
.Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
cell.Offset(0, 9).Resize(1, 70).Value = .Range("Range2").Value
End With

However, this simply repeats the FIRST observation from "Range1" 70 times
accross the the correct range in "Sheet1". I obviously need it to paste ALL
of the remaining 69 observations.

Your help is much appreciated.

 

[Tom Ogilvy]

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

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