Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas in range and pasting in a selection | Excel Worksheet Functions | |||
Copying and pasting from a range | Excel Worksheet Functions | |||
Problem copying a range to a different workbook | Excel Programming | |||
Problem copying range and pasting to multiple sheets | Excel Programming | |||
Finding a range for Copying & Pasting | Excel Programming |