Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a range of cells from one sheet to another
I want to copy a set of cells, the values, from one sheet to another
in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a range of cells from one sheet to another
Try:
Sheets("Compare").Range(Cells(x, 1), Cells(x, z)).value = _ Sheets("RawData").Range(Cells(y, 1), Cells(y, z)).value Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a range of cells from one sheet to another
Qualify your ranges:
with sheets("Compare") Set rng1 = .Range(.Cells(x, 1), .Cells(x, z)) end with with sheets("RawData") Set rng2 = .Range(.Cells(y, 1), .Cells(y, z)) end with rng1.Value = rng2.Value Those unqualified cells() will either refer to the activesheet (if it the code is in a general module) or they'll refer to the worksheet owning the code (if the code is behind a worksheet). Those leading dots in .range() and .cells() mean that they refer to the object in the previous with statement--in this case either sheets("Compare") or sheets("rawdata") You could have used: Set rng1 = Sheets("Compare").Range(Sheets("Compare").Cells(x, 1), _ Sheets("Compare").Cells(x, z)) Revolvr wrote: I want to copy a set of cells, the values, from one sheet to another in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a range of cells from one sheet to another
On Oct 7, 1:11*pm, Dave Peterson wrote:
Qualify your ranges: with sheets("Compare") * Set rng1 = .Range(.Cells(x, 1), .Cells(x, z)) end with with sheets("RawData") * Set rng2 = .Range(.Cells(y, 1), .Cells(y, z)) end with rng1.Value = rng2.Value Those unqualified cells() will either refer to the activesheet (if it the code is in a general module) or they'll refer to the worksheet owning the code (if the code is behind a worksheet). Those leading dots in .range() and .cells() mean that they refer to the object in the previous with statement--in this case either sheets("Compare") or sheets("rawdata") You could have used: Set rng1 = Sheets("Compare").Range(Sheets("Compare").Cells(x, 1), _ * * * * * * * * *Sheets("Compare").Cells(x, z)) Revolvr wrote: I want to copy a set of cells, the values, from one sheet to another in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx -- Dave Peterson Thanks. This method works. I guess the confusion is that .Cells works with either Range or Sheets, and I had assumed if the sheet of the range is specified, the Cells would be the same. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a range of cells from one sheet to another
This portion of your code:
Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) could be rewritten as: Sheets("Compare").Range(activesheet.Cells(x, 1), activesheet.Cells(x, z)) And if Compare isn't the activesheet, then that portion of the code will fail. It's not really the difference between .cells() and .range(). It's how each of them are qualified--and if you don't explicitly qualify them, then excel will use what it thinks is right. And that's not always a good thing! Revolvr wrote: <<snipped Thanks. This method works. I guess the confusion is that .Cells works with either Range or Sheets, and I had assumed if the sheet of the range is specified, the Cells would be the same. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy & Paste Across Sheet Tabs in a Range of Cells | Excel Discussion (Misc queries) | |||
code to copy a range of cells to another sheet | Excel Programming | |||
Macro to copy range of cells and paste into 1 sheet | Excel Programming | |||
Use macro to check a range of cells within a row and copy wanted data to new sheet | Excel Programming | |||
Use macro to check a range of cells within a row and copy wanted data to new sheet | Excel Programming |