CODEWITHSUNDEEP
excelvbanamed-ranges
findmeinthebreadaisle
findmeinthebreadaisle

Reputation: 1

Revise one part of many Named Ranges

I hope my description is clear.

I have an Excel workbook (Microsoft 365 Version 2211) with two Worksheets. In each worksheet, I've defined many individual cells to have names with a specific naming convention: CarBYWt_#### and ANBYWt_####. I now want to update the named ranges so that they are now InsBYWt_#### and ACOBYWt_####, respectively. As you can see, I only want to change the first part of my named range - nothing else. I don't want to change the cells they reference, only the beginning part of each name. I have over 100 names in each sheet so I think this would be best solved with a Macro.

I don't have a lot of VBA experience, so I started to do this manually but realize this will take me much too long. I'm hoping to learn from the community how I can do this myself in the future. Many, many thanks in advance.

Upvotes: 0

Views: 29

Answers (1)

Toddleson
Toddleson

Reputation: 4457

Loop through the Names Collection: For Each NameObj In ThisWorkbook.Names

Checking for your conditions: If NameObj.Name Like "CarBYWt_*" Then

You can rename them NameObj.Name = "InsBYWt_" & Split(NameObj.Name, "_")(1)

Sub Example()
    Dim NameObj As Variant
    For Each NameObj In ThisWorkbook.names
        If NameObj.Name Like "CarBYWt_*" Then
            NameObj.Name = "InsBYWt_" & Split(NameObj.Name, "_")(1)
        ElseIf NameObj.Name Like "ANBYWt_*" Then
            NameObj.Name = "ACOBYWt_" & Split(NameObj.Name, "_")(1)
        End If
    Next
End Sub

Upvotes: 1

Related Questions