Works behind sheet but not in module

[onechipshot]

The following code is part of a much larger piece and does a very simple task. It does what I want except fails to create the range name "TargetR"

Based on self support the problem is probably in identifying the workbook and sheet. Please help.

Private Sub AAATest()
Dim WS As Worksheet
Dim CellName As String
Dim cell As Range
Dim RangeName As String
Dim myCell As Range
Set WS = ThisWorkbook.Worksheets("Sheet1")
Worksheets("Sheet1").Select
Set myCell = Range(ActiveCell.Address)
RangeName = "TargetR"
CellName = myCell.Offset(0, 1).Address
Set cell = Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
End Sub

 

[HaHoBe]

Hi,

what about

Private Sub AAATest()
Dim ws As Worksheet
Dim rngCell As Range
Dim strRangeName As String

Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Activate
Set rngCell = ActiveCell.Offset(0, 1)
strRangeName = "TargetR"
ThisWorkbook.Names.Add Name:=ThisWorkbook, RefersTo:=rngCell

Set rngCell = Nothing
Set ws = Nothing

End Sub

I would not rely on working with ActiveCell as that is somehow unpredictable from the code posted here and I would incorporate a check if that name already exists in the workbook (this sample works with the ActiveWorkbook):

Sub CheckAddNamesInWorkbook()

  Dim oName As Name
  Dim strName As String
  Dim strRange As String
  
  strName = "TargetR"
  strRange = "Sheet1!$B$1:$D$20"

  For Each oName In ActiveWorkbook.Names
    If oName.Name = strName Then Exit Sub
  Next oName

  ActiveWorkbook.Names.Add Name:=strName, RefersTo:="=" & strRange

End Sub

Ciao,
Holger