Copy and paste between sheets

  • Thread starter Thread starter dmg
  • Start date Start date
D

dmg

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"
 
this should do what your example is trying to do

Sheets(1).Range(Cells(1, 1), Cells(1, 4)).Copy Sheets(2).Cells(1, 1)
 
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.
 
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.
 
Hi DMG,
That was it. I missed the context of the Cells property. It is the
Activesheet when it's not otherwise qualified.
Click to expand...

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.
 
If you need to qualify Range, then you need to qualify Cells as well.

With Sheets(1)
.Range(.Cells(1, 1), .Cells(1, 4)).Copy Sheets(2).Cells(1, 1)
End With
 
Back
Top