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

Try:

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

Tom

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

On Oct 7, 1:11*pm, Dave Peterson <peter...@verizonXSPAM.net> 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 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))
>
>
>
> 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.

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