DoubleClick worksheet macro to copy paste cell value to another sheet
 

Hi. I've got a macro the copies the value in whatever cell (varies)
when it is double clicked to another cell on another sheet (static,
E2). I recorded a macro without selecting the cell first (just
recorded the copy and paste to the specific cell). Afterwords, I made
a BeforeDoubleClick worksheet module. If I run this code as a regular
Sub macro, it works just fine. But when I try to run it as a
worksheet BeforeDoubleClick macro, I get a run-time error that says,
'Run-time error 1004: Select method of Range class failed.' The code
is below, and it fails at the Range("E2").Select line. I tried
Range("E2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False, but that also failed.

I Googled this and found that others have done this successfully, so
what is going wrong here with mine? Thanks for any insights.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Application.CutCopyMode = False
Selection.Copy
Sheets("InvestigatorPro").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

DoubleClick worksheet macro to copy paste cell value to anothersheet
 

hi,

if you want to copy only the value and not the format

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("InvestigatorPro").Range("E2") = Target.Value
End Sub


isabelle

Le 2015-05-11 21:00, Phrank a écrit :

Hi. I've got a macro the copies the value in whatever cell (varies)
when it is double clicked to another cell on another sheet (static,
E2). I recorded a macro without selecting the cell first (just
recorded the copy and paste to the specific cell). Afterwords, I made
a BeforeDoubleClick worksheet module. If I run this code as a regular
Sub macro, it works just fine. But when I try to run it as a
worksheet BeforeDoubleClick macro, I get a run-time error that says,
'Run-time error 1004: Select method of Range class failed.' The code
is below, and it fails at the Range("E2").Select line. I tried
Range("E2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False, but that also failed.

I Googled this and found that others have done this successfully, so
what is going wrong here with mine? Thanks for any insights.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Application.CutCopyMode = False
Selection.Copy
Sheets("InvestigatorPro").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub