Scope NamedRange to Multiple WorkSheets

Is there a way with VBA that you can scope the same NamedRange to multiple worksheets? I would like to set the Named Range "WPStatus" to cell B3 on every worksheet in my workbook.

I know I do this individually by naming and then scoping the specific worksheet. But can be done with VBA? I have about 50 sheets I would like to scope this to.

Then after I have them all scoped, I can then actually move/drag that cell to the correct place on the worksheet and reference the namedrange on each sheet as needed. The will all not reside in cell B3 when its all complete.

Originally Posted by mikerickson

Try
Name: NamedRange
RefersTo: =!$B$3

Then the formula =NamedRange will return the value from B3 of the sheet on which the formula resides.

However, dragging the cell will not change the reference to NamedRange. And NamedRange will refer to the same cell address on every sheet. NamedRange is a workbook scoped name.

If you want a VBA routine to set a worksheet scoped name for each sheet you could use.

Please Login or Register  to view this content.

When the cell is cut/paste, these (sheet scoped) names will adjust.

Thanks, I found what I think is also another simple way to do it like this too

Please Login or Register  to view this content.