Create Named Ranges on inactive sheets

old code which works but loops are bulky.

Please Login or Register  to view this content.

new code which despite sheet specified ranges tries to set the names to the active sheet.

Please Login or Register  to view this content.

Is there a way to work this new without using sheet.activate?

Edit: The issue is not that the code fails to execute. It is just not quite doing what I want VPM.Names.Add or DMU.Names.Add create sheet specific named ranges but VPM.Range.CreateNames appears to be ignoring the worksheet and just passing the addresses to the activesheet named ranges.

Yes the VPM line works fine because it is first. It is the DMU line that has issues because it pops up the "would you like to replace named ranges" box.
VPM and DMU are set as a public variable in the calling macro.
I should have mentioned that VPM and DMU have the same headers.

Please Login or Register  to view this content.

Can VBA change the scope of named ranges after they have been created?
I would be far more comfortable looping through a list of name objects than looping through range objects.

Originally Posted by Leon V (AW)

I would be far more comfortable looping through a list of name objects than looping through range objects.

Why? You would just be adding an unnecessary step.

Originally Posted by rorya

Why? You would just be adding an unnecessary step.

looping through ranges is very sensitive to user settings like R*C vs ?# or step to next row vs next column. That's why I make these named ranges, to avoid range referencing later in the code.
I know in this case it would be little difference (auto create and loop through vs loop through and create) however I still like to avoid bad habits where possible.

That makes no sense to me. You're processing the same ranges (referred to the same way) either way in this code, you're just adding unnecessary steps.

Originally Posted by Fluff13

I'm with rorya on this.
It's better to correctly name & reference the range when created than loop through them afterwards changing there scope.
Whilst I have no idea what the structure of your workbook is, with the code I supplied above where Vpm is Sheet2.
If you have named ranges on a sheet called Sheet21, they would also be changed to sheet scope.

There will only be 2 sheets (VPM & DMU) created by the add-in in a new document, both of them will have overlapping header names so all of them need to be sheet scope named ranges. Better the devil I know I guess, it's just really annoying when VBA can do 95% of what you want (almost capable is more frustrating than incapable).