[b]Help With Range Names[/b]

[soteman2005]

Hi,

For a specific project at work, I have been requested to create a spreadsheet using range names that has 3 identically structured sheets, one for inputs, one for overrides and one for the actual value to use. I know this isn't that logical but it has been specified that I do it like this. What I would like to do is prefix the input range names with FC_ (for financial console), then FCO_ and FCA_ for the overrides and actual but I don't want to have to type them out 3 times just to add one letter, especially as there are over 400 range names per sheet.

Is there a way to create a series of range names using the range names I have already set in the FC_, so that it copies and applies the existing range names to the same range on another sheet, but with the slightly changed prefix?

I have no idea apart from maybe I should use a loop function???

Any help would be greatly appreciated..

Thanks

Adam

 

[just_jon]

Did you know that if you create your range names as:

=!RangeName

with the exclamation mark but without any sheetname reference, that name is available for use on any sheet, and refers to the given range on that sheet?

Caveat: cannot be used in charting.

 

[BrianB]

With this basic structure it is possible to add/delete/change named ranges in a workbook using VBA.

This macro goes through *all* the named ranges set at workbook level and adds 3 more of the same starting with FC_ ,FCO_, FCA_ and referencing those worksheets.

You therefore need to make sure that you either start with a workbook containing only the names that you want, or add some sort of If ... Then to limit the selection (similar to my check). This is so critical that I have put a check at the beginning to ensure that the FC* names are not repeated. Back everything up - you have been warned.

Sub test()
    Dim FC As Worksheet
    Dim FCO As Worksheet
    Dim FCA As Worksheet
    Dim RangeName As String
    Dim RangeRefers As Range
    Dim NewName As String
    Dim NewRefers As String
    '------------------------
    Set FC = Worksheets("Sheet1")
    Set FCO = Worksheets("Sheet2")
    Set FCA = Worksheets("Sheet3")
    '-------------------------------
    '- names in master sheet
    '- add names starting with FC_ ,FCO_, FCA_
    For Each nm In ActiveWorkbook.Names
        RangeName = nm.Name
        Set RangeRefers = nm.RefersToRange
        '- check existing
        If Left(RangeName, 2) = "FC" Then
            rsp = MsgBox("Already have name " & RangeName & vbCr _
                & "Do you want to continue ?", vbYesNo)
            If rsp <> vbYes Then Exit Sub
        End If
        '- FC
        NewName = "FC_" & RangeName
        NewRefers = "='" & FC.Name & "'!" & RangeRefers.Address
        ActiveWorkbook.Names.Add _
            Name:=NewName, RefersTo:=NewRefers
        '- FCO
        NewName = "FC0_" & RangeName
        NewRefers = "='" & FCO.Name & "'!" & RangeRefers.Address
        ActiveWorkbook.Names.Add _
            Name:=NewName, RefersTo:=NewRefers
'        '- FCA
        NewName = "FCA_" & RangeName
        NewRefers = "='" & FCA.Name & "'!" & RangeRefers.Address
        ActiveWorkbook.Names.Add _
            Name:=NewName, RefersTo:=NewRefers
    Next
    '--------------------------
    MsgBox ("Done")
End Sub