Changing Local Names to Global Names

[LeoRI]

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]

I think this is what you want ..

'=============================================================================
'- 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]

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