I've got a simple copy and paste special procudure that runs fine as long as
the worksheet is active. But it I try to run it from a command button on
another worksheet, it crashes with the following error:

"Run-time error 1004: Application-defined or object-defined error."

Code execution stops on the second line below:

Worksheets("apps").Range("A2:B2").Copy
Worksheets("apps").Range(Cells(3, 1), Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues

The thing that puzzles me about this is that I could understand why I would
get an error if I was referring to this worksheet as "ActiveSheet" or if I
was using the Select method to select the ranges, but I'm not. I'm
referring to the worksheet explicitly by name in the Worksheets collection,
and I'm not using the Select method anywhere in this Sub procedure.

Can anyone tell me why I would need to have this worksheet be active in
order for this code to run, and also how I should modify the above code so
it will run when that worksheet is not active?

Thanks in advance.

Paul

Paul

try:

Sub test()
Dim numrows As Long
numrows = 5
With Worksheets("apps")
.Range("A2:B2").Copy
.Range(.Cells(3, 1), _
.Cells(numrows, 2)).PasteSpecial _
Paste:=xlPasteValues
End With
End Sub

Regards

Trevor


"Paul James" wrote in message
...
I've got a simple copy and paste special procudure that runs fine as long
as
the worksheet is active. But it I try to run it from a command button on
another worksheet, it crashes with the following error:

"Run-time error 1004: Application-defined or object-defined error."

Code execution stops on the second line below:

Worksheets("apps").Range("A2:B2").Copy
Worksheets("apps").Range(Cells(3, 1), Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues

The thing that puzzles me about this is that I could understand why I
would
get an error if I was referring to this worksheet as "ActiveSheet" or if I
was using the Select method to select the ranges, but I'm not. I'm
referring to the worksheet explicitly by name in the Worksheets
collection,
and I'm not using the Select method anywhere in this Sub procedure.

Can anyone tell me why I would need to have this worksheet be active in
order for this code to run, and also how I should modify the above code so
it will run when that worksheet is not active?

Thanks in advance.

Paul

Hi Paul,

I think the problem is your use of the unqualified Cells method in the
following line:

Worksheets("apps").Range(Cells(3, 1), Cells(numRows, 2)).PasteSpecial

If you don't qualify the Cells method with the worksheet it's supposed to
refer to then it refers to the active worksheet by default. Therefore, if
the apps worksheet is not active when this line of code is run, the Range
method and the Cells methods above are referring to two different worksheet.
Try it like this:

With Worksheets("apps")
.Range("A2:B2").Copy
.Range(.Cells(3, 1), .Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues
End With

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Paul James" wrote in message
...
I've got a simple copy and paste special procudure that runs fine as long
as
the worksheet is active. But it I try to run it from a command button on
another worksheet, it crashes with the following error:

"Run-time error 1004: Application-defined or object-defined error."

Code execution stops on the second line below:

Worksheets("apps").Range("A2:B2").Copy
Worksheets("apps").Range(Cells(3, 1), Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues

The thing that puzzles me about this is that I could understand why I
would
get an error if I was referring to this worksheet as "ActiveSheet" or if I
was using the Select method to select the ranges, but I'm not. I'm
referring to the worksheet explicitly by name in the Worksheets
collection,
and I'm not using the Select method anywhere in this Sub procedure.

Can anyone tell me why I would need to have this worksheet be active in
order for this code to run, and also how I should modify the above code so
it will run when that worksheet is not active?

Thanks in advance.

Paul

The Range property usage is fine. It is the Cells property is what is
missing the explicit reference:

Worksheets("apps").Range(Worksheets("apps").Cells( 3, 1),
Worksheets("apps").Cells(numRows, 2)).PasteSpecial Paste:=xlPasteValues

You might find it easier to define an object variable for the worksheet and
use that instead:
********
Dim wks as Worksheet

Set wks = Worksheets("apps")
wks.Range("A2:B2").Copy
wks.Range(wks.Cells(3, 1), wks.Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues
*******
Hope this helps,
--
George Nicholson

Remove 'Junk' from return address.


"Paul James" wrote in message
...
I've got a simple copy and paste special procudure that runs fine as long
as
the worksheet is active. But it I try to run it from a command button on
another worksheet, it crashes with the following error:

"Run-time error 1004: Application-defined or object-defined error."

Code execution stops on the second line below:

Worksheets("apps").Range("A2:B2").Copy
Worksheets("apps").Range(Cells(3, 1), Cells(numRows, 2)).PasteSpecial
Paste:=xlPasteValues

The thing that puzzles me about this is that I could understand why I
would
get an error if I was referring to this worksheet as "ActiveSheet" or if I
was using the Select method to select the ranges, but I'm not. I'm
referring to the worksheet explicitly by name in the Worksheets
collection,
and I'm not using the Select method anywhere in this Sub procedure.

Can anyone tell me why I would need to have this worksheet be active in
order for this code to run, and also how I should modify the above code so
it will run when that worksheet is not active?

Thanks in advance.

Paul

Ah, the finer points of VBA. You were right gentlemen, I needed to insert
an explicit reference to the worksheet so that VBA would understand which
worksheet contained the Cells.

Thank you for showing me how to overcome that problem, and for illuminating
this subtle point about object references.

All my best,

Paul