Module Script works. Double click post does not work

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
I have this script which works when run as a module script by pressing a button:

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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,356
Members
412,320
Latest member
sixnine0312
Top