Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_beforedoubleclick
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_beforedoubleclick
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. -- Regards KC "OssieMac" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub Worksheet_BeforeDoubleClick from elsewhere? | Excel Discussion (Misc queries) |