[Resolved] Naming Cell, dynamically defined by string

[jkmyoung]

Hi, I'm attempting to name a particular cell, based on a string.

  Dim CellName As String
  Dim extendedName As String
  
' Somewhere CellName has been set, eg
  CellName = "H25"

  ActiveWorkbook.Names.Add Name:="Total", RefersToR1C1:= _
        "='Report 5'!" & CellName

This gives me extra apostrophes around CellName. eg:
Total ='Report 5'!'H25'

So I tried defining the whole string first:

  extendedName = "'Report 5'!" & CellName
  ActiveWorkbook.Names.Add Name:="Total", RefersToR1C1:= extendedName

However, this then added quotation marks around the entire formula, eg:
Total ="'Report 5'!H25"

Does anyone know how to do this? It seems like I'm overlooking something really simple.

[VBAhack]

Welcome to the forums!

Try this:

cellname = "$H$25"
ActiveWorkbook.Names.Add Name:="Total", RefersTo:="=Report!" & cellname

The $'s are needed, otherwise the reference will be relative to cell position.

[jkmyoung]

Ok. That's a good tip. It doesn't entirely solve the problem, there are still extra quotes or apostrophes either way.
I think the problem may be more related to the fact that my sheet name needs to be surrounded in single quotes. Unfortunately, I am unable to rename this sheet.

Update:
ok removing the R1C1 at the end fixed it. Thanks.
How does one resolve a thread?