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