Range Name define and transfer within macro

[Roncondor]

hi - thank you - I always get great responses on Mr Excel

This is an easy question, but having trouble finding the solution

I want to name a range based on variables that have the Sheet name, Range that I want to define, Name that I want to assign

The code below does not work.

Also, what would the code be to duplicate a range name (not change) so that the oldname stays and the new names is also there

THANK YOU

Dim R As String
Dim NewName As String
Dim OldName As String
Dim TheSheet As String
Dim TheCells As String

NewName = "TheNewName"
TheSheet = "Sheet1"
TheCells = "$X$1:$Z$2"
R = "'" + TheSheet + "'!" & TheCells

ActiveWorkbook.Names.Add Name:=NewName, RefersTo:=Range(R)

 

[Norie]

It works fine for me, creating a named range called 'TheNewName' which refers to X1:Z2 on Sheet1 and has workbook scope.

How is it not working for you?

 

[Roncondor]

Not sure - will look into it -thank you

Do you have code to duplicate a range name?
i.e. I want range name X to be named Y (in additon to X)

 

[Roncondor]

Figured it out - but here is the answer in case will help someone else

Dim Oldname as string
Dim NewName as string
Dim A as string
Dim W as string
Dim R as string

Oldname ="OldName"
Newname = "Newname"

A = Range(OldName).Address
W = Range(OldName).Worksheet.Name
R = "'" + W + "'!" & A 'need the single quote in case the sheet name has spaces

ActiveWorkbook.Names.Add Name:=newname, RefersTo:=Range(R)