Copy Range

[Guest]

Hi,

I am trying to copy a range from one worksheet to another worksheet - in a
macro.
Is there anyway to save a range as a variable and then copy over the range.

Like

Dim Range1 as range

Range1= Range("A1:A100").value

Is there a good way to do this - right now I am using the copy paste command
in the macro - but there probably is a better way.

Thanks for your help

 

[JE McGimpsey]

One way:

Range("A1:A100").Copy Destination:=Sheets("Sheet2").Range("A1")

or, to copy just values:

Sheets("Sheet2").Range("A1:A100").Value = Range("A1:A100").Value

or perhaps:

With Sheets("Sheet1").Range("A1:A100")
Sheets("Sheet2").Range("A1").Resize( _
.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Dave Peterson]

Dim Rng1 as range
dim Rng2 as range

set rng1 = worksheets("sheet1").range("a1:A100")
set rng2 = worksheets("sheet2").range("d9") '<-- a single cell

rng1.copy _
destination:=rng2

==========
or

Dim Rng1 as range
dim Rng2 as range

set rng1 = worksheets("sheet1").range("a1:A100")
set rng2 = worksheets("sheet2").range("d9") '<-- a single cell

rng1.copy
rng2.pastespecial paste:=xlpaste values

=====
or
Dim Rng1 as range
dim Rng2 as range

set rng1 = worksheets("sheet1").range("a1:A100")
set rng2 = worksheets("sheet2").range("d9") '<-- a single cell

rng2.resize(rng1.rows.count,rng1.columns.count).value = rng2.value

===
The first copies everything--formulas, formats...
The last two just work with values.

 

[Dave Peterson]

Just a typo:

rng2.resize(rng1.rows.count,rng1.columns.count).value = rng2.value
should be
rng2.resize(rng1.rows.count,rng1.columns.count).value = rng1.value