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

[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

 

[TomPl]

Try:

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

Tom

 

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

 

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

 

[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!