Changing Local Names to Global Names

LeoRI

New Member
Joined
Oct 27, 2008
Messages
2
I am creating an application using Excel 2003. I have already created a workbook that contains a lot of cells with data validation linked to lists which are all stored on a sheet named List Maintenance. Each of the individual lists is named with a range name. The validation settings refer to these range names and they all work fine in this workbook.

There are going to be other workbooks that will need to use these same lists. So, I created a procedure to export the List Maintenance sheet to a separate file so that it can easily be imported into other workbooks as necessary. I then started working on the next workbook and created a procedure to import the List Maintenance sheet. But now the range names on that sheet have become Local names rather than Global names so I cannot refer to them with data validation, unless the data validation cells are on the same sheet, which they will not be.

Is there a way to programmatically convert the Local names back to Global names once the sheet is imported? If not, do you have any recommendations on how to proceed?

Thanks for your time.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I think this is what you want ..
Code:
'=============================================================================
'- MAKE WORKBOOK LEVEL RANGE NAMES TO MATCH SHEET LEVEL RANGE NAMES
'- Range names in a worksheet contain the sheet name which needs to be removed
'- Method : find "!" in the name and remove characters up to there
'- Brian Baulsom November 2008
'=============================================================================
Sub test()
    Dim NM As Name
    Dim NMname As String
    Dim NMref As String
    '-------------------------------------------------------------------------
    With Worksheets("Sheet1")
        MsgBox (.Names.Count & " names.")
        '---------------------------------------------------------------------
        '- loop names in sheet
        For Each NM In .Names
            '-----------------------------------------------------------------
            '- Range name
            NMname = NM.Name
            NMname = Right(NMname, Len(NMname) - InStr(1, NMname, "!", vbTextCompare))
            '----------------------------------------------------------------
            '- Name refersTo
            NMref = NM.RefersTo
            ActiveWorkbook.Names.Add Name:=NMname, RefersTo:=NMref
        Next
    End With
End Sub
'=============================================================================

You might find my code here of interest. Lists workbook names into a worksheet.
http://www.mrexcel.com/forum/showthread.php?t=289915
 

LeoRI

New Member
Joined
Oct 27, 2008
Messages
2
Thanks Brian!

I will keep that code for future use.

I found a work around on this project having the code in the originating workbook open the target worbooks, delete the sheet containing the ranges, insert a new sheet, copy the ranges from the destination workbook to the target and create the names in the target.

Your code would have been much simpler.

Leo
 

Forum statistics

Threads
1,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top