Problem with my DoubleClick event

[chamdan]

Hi,
I have the following code in the Wortksheet_Doublick, which give an error 1004 when debugging.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range

    Set isect = Intersect(Target, Range("I:I"))
    Range("A" & Target.Row & ":I" & Target.Row).Select
    Selection.Copy

    Sheets("Sheet1").Select
    Range("A" & Target.Row & ":I" & Target.Row).Select   '<----------- This is where the error is.

    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Am I doing something wrong?


Chuck

[HansV]

In a worksheet module, the line

Range("A" & Target.Row & ":I" & Target.Row).Select

refers to the sheet running the code. But since you selected Sheet1, you cannot select a range on the sheet running the code.

Solution: avoid selecting cells.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Range("A" & Target.Row & ":I" & Target.Row).Copy
    Sheets("Sheet1").Range("A" & Target.Row & ":I" & Target.Row).PasteSpecial _
        Paste:=xlPasteAllUsingSourceTheme
    Application.CutCopyMode = False
End Sub

I removed the lines involving iSect since you don't do anything with the range iSect.

[Rudi]

Try this version without the select method which was giving the unexpected error:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range
Dim lRow As Long
    Set isect = Intersect(Target, Range("I:I"))
    Range("A" & Target.Row & ":I" & Target.Row).Copy
    Sheets("Sheet1").Range("A" & Target.Row & ":I" & Target.Row).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Application.CutCopyMode = False
End Sub

[chamdan]

Thanks! It worked.

Cheers!

Chuck