Simple Copy-Paste logic not working

[Brian]

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

 

[Guest]

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]

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

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

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