Force VBA to use the Global Definition of the named range
I have a workbook with 2 names defined with the same name
One is a Global "RngSales" And the other is local to a Sheet Named "Builders" which is the first sheet in the WorkBook.
See the Picture for the Name Manager Dialog view showing the names are defined
I want to use the Global version but don't seem to be able to reference it
In the immediate window
?Workbooks("Sales Summary11-12.xlsm").Names(
Gives
=Builders!$A$6 (Which is the local version)
Global Name belonging to the workbook & so expected was
=RV!$A$6
I would have expected the result for
?WorkSheets("Builders").Na
=Builders!$A$6 (Which it is)
How do I reference the name so it uses Global which is addressed on a different sheet & I get the result as per the picture
=RV!$A$6
One is a Global "RngSales" And the other is local to a Sheet Named "Builders" which is the first sheet in the WorkBook.
See the Picture for the Name Manager Dialog view showing the names are defined
I want to use the Global version but don't seem to be able to reference it
In the immediate window
?Workbooks("Sales Summary11-12.xlsm").Names(
Gives
=Builders!$A$6 (Which is the local version)
Global Name belonging to the workbook & so expected was
=RV!$A$6
I would have expected the result for
?WorkSheets("Builders").Na
=Builders!$A$6 (Which it is)
How do I reference the name so it uses Global which is addressed on a different sheet & I get the result as per the picture
=RV!$A$6
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm
Your tip for how the names are selected enabled me to make a work around so you can have this
Your tip for how the names are selected enabled me to make a work around so you can have this
your a gent :) Thank you
ASKER
To Clarify
The first of the local(or possibly many locals) OR the Global if thats first is the one returned when a local is called & both Locals & golbals exist with the same name
So it depends really on the sheet name
I had hundreds of these & was using code to rewrite the names as locals so had to to remap all as local so I remapped the Globals the a sheet names zzz but using the same definitions apart from the sheet name and that forced it to use the local defs
I suspect even if the names were hidden it would work this way but haven't tested
The first of the local(or possibly many locals) OR the Global if thats first is the one returned when a local is called & both Locals & golbals exist with the same name
So it depends really on the sheet name
I had hundreds of these & was using code to rewrite the names as locals so had to to remap all as local so I remapped the Globals the a sheet names zzz but using the same definitions apart from the sheet name and that forced it to use the local defs
I suspect even if the names were hidden it would work this way but haven't tested
ASKER
Safer & hours of work saved if I dont have to which is why I want this if it exists.
I will submit a bug report to MS and see what they say.