Worksheet_beforedoubleclick
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" wrote:
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
|