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