My Aswer Is This
Well-known Member
- Joined
- Jul 5, 2014
- Messages
- 19,510
- Office Version
- 2021
- Platform
- Windows
I have this script which works when run as a module script by pressing a button:
But then I have this same script as a double click script but it causes a error when I double click on a cell with a proper named range in the cell I double click on:
I get this error code:
Run Time error 1004
Method Range of object
Worksheet Failed
Any help here on why this would happen.
VBA Code:
Sub Range_Name()
'Modified 10/16/2020 3:55:51 AM EST
Dim sn As String
Dim ans As String
ans = ActiveCell.Value
sn = Range(ans).Worksheet.Name
Sheets(sn).Range(ans).Copy Sheets(1).Range("B11")
End Sub
But then I have this same script as a double click script but it causes a error when I double click on a cell with a proper named range in the cell I double click on:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim sn As String
Dim ans As String
ans = Target.Value
sn = Range(ans).Worksheet.Name
Sheets(sn).Range(ans).Copy Sheets(1).Range("B11")
End Sub
I get this error code:
Run Time error 1004
Method Range of object
Worksheet Failed
Any help here on why this would happen.