Worksheet_beforedoubleclick

[KC]

Hi

I wish to be advised why it failed, and how to rectify please.

Listing A was tested working as expected.
sub t()
Rows("9:9").Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
end sub

Once this sub was transferred to Worksheet_BeforeDoubleClick, it failed with
Run-time Error "1004"
Select method of range class failed

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select <<<<<<<<
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Also I do not seem to be able to condense the code without using so many
"Select".
Are these restriction applicable to event handling please?

Thank you for your advice
Regards
KC

 

[Guest]

Hi KC,

Two examples. The first is your code edited. The second to answer your
second question:-

I don't really know why ActiveSheet is required to select the paste cell but
it won't work without it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy
Sheets(Range("D1").Text).Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Second Example. If Sheets("CData") is where the macro is called from then
the last line
Sheets("CData").Select
is not required.

Note that the copy and destination is one line. The space and underscore
after copy is only a line break in the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy _
Destination:=Sheets(Range("D1").Text) _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Sheets("CData").Select
End Sub


Regards,

OssieMac

 

[KC Cheung]

Hi OssieMac

Yes, both tested OK
I come across quite often unwritten rules we have to "know" before the lines
will work.
Before we "know" it, it is simply frustrating.