Copy and paste between sheets
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"
|