copying and pasting range problem
 

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.

copying and pasting range problem
 

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

--
Regards,
Tom Ogilvy


"Henrik" wrote in message
...
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.