Paste range from one sheet to another

[Dan]

I copied a defined range("VarRange") from sheet1 and I
would like to paste it to sheet2.

I activated sheet2 and select (Range("A3").Select) as the
destination. My Paste line is this: Range("VarRange").Paste

No Go. R-T errorbject does not support this property or
method. I need help pastein'! Dan

 

[mangesh_yadav]

.Paste is not available. Use .PasteSpecial

- Manges

 

[Guest]

The following code loads the values in the cells that the defined name refers
in to a variant array and then uses another varient array to identify the
boundaries of the originsal defined range. This second array is unneccessary
if you are pasting into exactly the name values into the same position on the
second worksheet but if not you can use it to specify a new address.

Sub alpha()
' BASED ON a3 BEING THE TARGET CELL
' Declare the variables
Dim x As Variant, y As Variant
Dim r As Long, c As Long
' Load the name values into variant x
x = Range(ThisWorkbook.Names("VarRange").RefersTo)
' identify the boundaries of the range
y = Names("VarRange").RefersToRange.Value
' Reposition the target range
r = UBound(y, 1) + 2 'DEPENDS ON TRG ROW
c = UBound(y, 2) = 0 'DEPENDS ON TRG COL
' Spit the names values into the target
Sheets("Sheet2").Range(Cells(3, 1), Cells(r, c)) = x
End Sub

Hope this helps,

Alasdair Stirling

 

[Tom Ogilvy]

Range("VarRange").Copy Destination:= _
Worksheets("Sheet2").Range("A3")

You don't need to select in most cases work with ranges.