VBA code that only runs when a worksheet is active
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
|