Creating an Excel Named Range programatically

[murad5]

Hi Everyone,

I'm writing a procedure to create a workbook on demand. My problem is when I try to name a dynamic range programatically. This is the code I'm using:

ThisWorkbook.Names.Add Name:="ProdLines", _
RefersTo:="=OFFSET('ProdLines'!$A$1,1,1,COUNTA('ProdLines'!$A:$A)-1,1)"

This creates the named range ok, but the scope of the name seems to be limited to the sheet on which it was created, which means I can't reference it on any other sheets. Does anyon have an idea how I can get around this?

Thanks...

 

[SkipVought]

Hi,

ThisWorkbook.Names.Add _
  Name:="ProdLines", _ 
  RefersTo:="='" & ActiveSheet.Name & "'!" & [A1].CurrentRegion.Address

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Actually, I think that this is what you were looking for

ThisWorkbook.Names.Add _
  Name:="ProdLines", _ 
  RefersTo:="=OFFSET('" & ActiveSheet.Name & "'!$A$1,1,1,COUNTA('" & ActiveSheet.Name & "'!$A:$A)-1,1)"

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[murad5]

Hi Skip - thanks for your quick response. Unfortunately, I'm still getting the same issue. It seems as if the named ranges are invisible if the sheet where the range they're located is not active. I can't access them either through Excel's Insert - Name - Define... dialog box or through VB. One interesting thing is that, when they are visible in the dialog box (ie - when the sheet they're on is active) the dialog box shows the sheet name to the right of the range name.

Since they're unlikely to change size once the file is constructed, I may just change them from dynamic arrays to static arrays.

 

[TonyJollans]

Hi murad5,

What am I missing here?

If I create a name using your code (or Skip's), I can see it from any sheet in the workbook.

What version of Excel are you using?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[murad5]

I'm using Excel 2002. I have to say, I've never had this problem before.