Renaming a lot of table arrays quickly.

[snets123]

Hi there,
I have a tricky situation. I have around 600 table arrays in a speadsheet that I must rename with specific GUIDS that I have. I know the technical way of going to Formulas-->Define Names-->Name Manager-->Selecting the table I want to rename and than entering the new name. Would anyone know of a way to do this that would help me from not having to do it manually? Some form of a vlookup..? Maybe a macro?

 

[Alex Blakenburg]

No problem, one of us will give you a macro you can use tomorrow.

 

[snets123]

No problem, one of us will give you a macro you can use tomorrow.

Thank you very much!

 

[JEC]

Assuming your data on sheet "rename" is a real table:
Try this first on a copy of your workbook.

Sub jec()
   ar = Sheets("Rename").ListObjects(1).DataBodyRange
   For i = 1 To UBound(ar)
      ThisWorkbook.Sheets("ALLSCALES").ListObjects(ar(i, 1)).Name = ar(i, 2)
   Next
End Sub

 

[johnnyL]

The version I came up with:

Sub RenameListOfTableNames()
'
    Dim LastRowOfTableNames     As Long
    Dim StartingRowOfNames      As Long
    Dim TableCounter            As Long
    Dim OldTableNamesArray()    As Variant
    Dim NewTableNamesArray()    As Variant
    Dim WS                      As Worksheet
'
    Set WS = Sheets("Rename")                                                                           ' <--- Change to sheet name desired
    StartingRowOfNames = 2                                                                              ' <--- Set this to the starting row of Table names
'
    LastRowOfTableNames = WS.Range("A" & Rows.Count).End(xlUp).Row                                      ' Find the row of the last Table Name

    OldTableNamesArray = Application.Transpose(WS.Range("A" & StartingRowOfNames & ":A" & LastRowOfTableNames)) ' Load current table names into array
    NewTableNamesArray = Application.Transpose(WS.Range("B" & StartingRowOfNames & ":B" & LastRowOfTableNames)) ' Load the new table names into array

    For TableCounter = 1 To UBound(OldTableNamesArray)                                                  ' Loop through Table Names to be renamed
        Sheets("ALLSCALES").ListObjects(OldTableNamesArray(TableCounter)).Name = NewTableNamesArray(TableCounter)   '   Rename each Table name
    Next
End Sub

 

[snets123]

The version I came up with:

Sub RenameListOfTableNames()
'
    Dim LastRowOfTableNames     As Long
    Dim StartingRowOfNames      As Long
    Dim TableCounter            As Long
    Dim OldTableNamesArray()    As Variant
    Dim NewTableNamesArray()    As Variant
    Dim WS                      As Worksheet
'
    Set WS = Sheets("Rename")                                                                           ' <--- Change to sheet name desired
    StartingRowOfNames = 2                                                                              ' <--- Set this to the starting row of Table names
'
    LastRowOfTableNames = WS.Range("A" & Rows.Count).End(xlUp).Row                                      ' Find the row of the last Table Name

    OldTableNamesArray = Application.Transpose(WS.Range("A" & StartingRowOfNames & ":A" & LastRowOfTableNames)) ' Load current table names into array
    NewTableNamesArray = Application.Transpose(WS.Range("B" & StartingRowOfNames & ":B" & LastRowOfTableNames)) ' Load the new table names into array

    For TableCounter = 1 To UBound(OldTableNamesArray)                                                  ' Loop through Table Names to be renamed
        Sheets("ALLSCALES").ListObjects(OldTableNamesArray(TableCounter)).Name = NewTableNamesArray(TableCounter)   '   Rename each Table name
    Next
End Sub

It was a success!
Thanks for all your help @johnnyL, @*JEC , @Alex Blakenburg

 

[Alex Blakenburg]

Glad we could help. Thanks for the feedback.