How to copy a range of cells from one sheet to another

R

Revolvr

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
 
T

TomPl

Try:

Sheets("Compare").Range(Cells(x, 1), Cells(x, z)).value = _
Sheets("RawData").Range(Cells(y, 1), Cells(y, z)).value

Tom
 
D

Dave Peterson

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))
 
R

Revolvr

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 thecode
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))


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.
 
D

Dave Peterson

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top