Simple Copy-Paste logic not working
Here is my block of code.
<<< Dim ws As Worksheet Dim rowId as Integer Set ws = ThisWorkbook.Worksheets("Sheet1") For rowId = 1 to 10 ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Copy _ ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Offset(1, 0) '[fails at this step!!!] Next rowId <<< It works on my machine at home... but fails on my work computer. Both machines are XP Pro SP2 with Office 2003 installed. The error I get is : Runtime error 1004 - Method 'Range' of object '_Worksheet' failed I've seen the problem before, but can't remember what/why it doesn't work. Any help would be greatly appreciated. Brian |
Simple Copy-Paste logic not working
It works for me so long as the active sheet is "Sheet1". I supect on your
computer at work you had another sheet active. The problem is that the Cells method defaults to the active sheet if it is not qualified even if it is contained within a Range statement. Should be: ws.Range(ws.Cells(rowId, 1), ws.Cells(rowId, 2)).Copy _ ws.Range(ws.Cells(rowId, 1), ws.Cells(rowId, 2)).Offset(1, 0) It's not necessary to use a loop to populate a range with a constant value nor the Copy method if all you want is to transfer values. You can just define the range and specify the value(s). This example accomplishes the same as your code: Sub Test() Dim r As Range Set r = Sheets("Sheet1").Range("A1:B1") r.Offset(1).Resize(10, 2).Value = r.Value End Sub Regards, Greg "Brian" wrote: Here is my block of code. <<< Dim ws As Worksheet Dim rowId as Integer Set ws = ThisWorkbook.Worksheets("Sheet1") For rowId = 1 to 10 ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Copy _ ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Offset(1, 0) '[fails at this step!!!] Next rowId <<< It works on my machine at home... but fails on my work computer. Both machines are XP Pro SP2 with Office 2003 installed. The error I get is : Runtime error 1004 - Method 'Range' of object '_Worksheet' failed I've seen the problem before, but can't remember what/why it doesn't work. Any help would be greatly appreciated. Brian |
Simple Copy-Paste logic not working
Thanks Greg...
I finally tripped on it after trying several different approaches. My test code at home was just using the active sheet. At work, I was applying the copy to a non-active sheet. I don't think I tried to do the full qualification... but I'll give it a try. And as for the full range copy.... That looks like a good enhancement as well, although I'm not sure that will pick up the formatting in that cell. I'm trying to add the cell borders as I add data to a worksheet. The copy method seems to work the best for that... verses making the "border" calls for each cell edge. Thanks for the response. Brian "Greg Wilson" wrote in message ... It works for me so long as the active sheet is "Sheet1". I supect on your computer at work you had another sheet active. The problem is that the Cells method defaults to the active sheet if it is not qualified even if it is contained within a Range statement. Should be: ws.Range(ws.Cells(rowId, 1), ws.Cells(rowId, 2)).Copy _ ws.Range(ws.Cells(rowId, 1), ws.Cells(rowId, 2)).Offset(1, 0) It's not necessary to use a loop to populate a range with a constant value nor the Copy method if all you want is to transfer values. You can just define the range and specify the value(s). This example accomplishes the same as your code: Sub Test() Dim r As Range Set r = Sheets("Sheet1").Range("A1:B1") r.Offset(1).Resize(10, 2).Value = r.Value End Sub Regards, Greg "Brian" wrote: Here is my block of code. <<< Dim ws As Worksheet Dim rowId as Integer Set ws = ThisWorkbook.Worksheets("Sheet1") For rowId = 1 to 10 ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Copy _ ws.Range(Cells(rowId, 1), Cells(rowId, 2)).Offset(1, 0) '[fails at this step!!!] Next rowId <<< It works on my machine at home... but fails on my work computer. Both machines are XP Pro SP2 with Office 2003 installed. The error I get is : Runtime error 1004 - Method 'Range' of object '_Worksheet' failed I've seen the problem before, but can't remember what/why it doesn't work. Any help would be greatly appreciated. Brian |
All times are GMT +1. The time now is 06:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com