Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line copy - Not just a simple copy paste | Excel Worksheet Functions | |||
Simple Copy and Paste | Excel Programming | |||
Simple Copy & Paste | Excel Programming | |||
Help! What's wrong with this simple copy and paste | Excel Programming | |||
Simple copy and paste | Excel Programming |