1

How do I reference a named range on the ActiveSheet using VBA? I have several worksheets, each with a named range that uses the same title. None of the below work:

ActiveSheet.NamedRange
ActiveSheet!NamedRange
Nat Aes
  • 887
  • 5
  • 18
  • 34

2 Answers2

3

You need to use:

ActiveSheet.Range("myNamedRange")

or just

Range("myNamedRange")

it's the same for ActiveSheet

If you need to get named range from another worksheet, use following code:

Worksheets("Sheet1").Range("myNamedRange")
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • 1
    Thanks for this. To be more exact I am trying to use VBA to create a chart with the series values set to the named range. I have used the following code: ActiveChart.SeriesCollection.New Series, followed by ActiveChart.SeriesCollection(1).Values ="=ActiveSheet.Range("NamedRange")". However I get a syntax error. Any ideas? – Nat Aes Jan 15 '14 at 18:25
  • I think this question is solved. For your second question see my answer here: http://stackoverflow.com/questions/21135226/vba-referencing-named-range/21145700#21145700 – Dmitry Pavliv Jan 15 '14 at 21:21
1

You may also reference it by bracket, example:

[my_table]

or

ActiveSheet.[my_table]

and you avoid using the quotation marks.

Tony Diaz
  • 41
  • 1