Copy and paste between sheets
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" |
Copy and paste between sheets
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) -- Gary "dmg" wrote in message nk.net... 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" |
Copy and paste between sheets
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" -- Dave Peterson |
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" |
Copy and paste between sheets
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" |
Copy and paste between sheets
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 -- Regrards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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) -- Gary "dmg" wrote in message nk.net... 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" |
All times are GMT +1. The time now is 08:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com