Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Range
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Range
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 In article , Jeff wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Range
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).va lue = rng2.value === The first copies everything--formulas, formats... The last two just work with values. Jeff wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Range
Just a typo:
rng2.resize(rng1.rows.count,rng1.columns.count).va lue = rng2.value should be rng2.resize(rng1.rows.count,rng1.columns.count).va lue = rng1.value Dave Peterson wrote: 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).va lue = rng2.value === The first copies everything--formulas, formats... The last two just work with values. Jeff wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy a specified name range | New Users to Excel | |||
How do I copy formulas but using the same range each time I copy | Excel Worksheet Functions | |||
help with range copy | Excel Discussion (Misc queries) | |||
copy a range without the same value | Excel Discussion (Misc queries) | |||
Copy a range | Excel Worksheet Functions |