Hi DMG,

That was it. I missed the context of the Cells property. It is the
Activesheet when it's not otherwise qualified.

As Dave explicitly indicated, this is true for general modules. If the code
were to reside in a sheet module, unqualified range expressions would be
implicitly qualified to refer to the sheet holding the code, irrespective of
whether it was active or not.

---
Regards,
Norman



"dmg" wrote in message
ink.net...
That was it. I missed the context of the Cells property. It is the
Activesheet when it's not otherwise qualified. Thanks for the help.


Dave Peterson wrote:
In a general module, unqualified ranges will be treated as though they
belong on
the active sheet.

So just this portion:

... = Sheets(1).Range(cells(1,1), cells(1,4)).Value
is equivalent to:
... = Sheets(1).Range(activesheet.cells(1,1),
activesheetcells(1,4)).Value

And if the activesheet isn't sheets(1), then you're going to have
trouble.

You could use:
... = Sheets(1).Range(sheets(1).cells(1,1), sheets(1).cells(1,4)).Value
to stop the error.

But this will cause an error:
Sheets(2).Range(cells(1,1)).Value = ...

You could use:

Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 1)).Value = ...
or just
Sheets(2).cells(1,1).Value = ...

=========

Now a question...

This "worked" for me:
Sheets(2).Range("A1").Value = Sheets(1).Range("A1:D1").Value

But only A1 of sheets(2) got changed. From the looks of your code, I'd
bet that
isn't what you wanted.

I'd do something like:

dim myRngToCopy as range
dim DestCell as range

with sheets(1)
set myrngtocopy = .range("A1:d1") 'some multicell range
end with

with sheets(2)
set destcell = .range("A1") 'still a single cell
end with

destcell.resize(myrngtocopy.rows.count,myrngtocopy .columns.count).value _
= myrngtocopy.value

or with myRngtocopy
destcell.resize(.rows.count,.columns.count).value _
= .value
end with

(to save a little typing.







dmg wrote:

I need to copy data in one sheet to another. The data to be copied is
of different sizes, may be in different locations on the source sheet
and has varying strings that define the top-left and bottom-right cells
depending on the situation. And the sheet names will also vary.

To simplify the problem, I have removed the variables and have inserted
number in the cell ranges. I need to understand why this works:

Sheets(2).Range("A1").Value = Sheets(1).Range("A1:D1").Value

and this produces a '1004' Application defined or Object defined error

Sheets(2).Range(cells(1,1)).Value = Sheets(1).Range(cells(1,1),
cells(1,4)).Value

I have also tried:

Sheets(1).Select
Sheets(1).Range(Cells(1, 1), Cells(1, 4)).Select
Selection.Copy
Sheets(2).Select
Range(Cells(1, 1)).Select
ActiveSheet.Pastevalue

But this produces a 1004 error at the line "Range(Cells(1, 1)).Select"