VBA to RENAME multiple TABLES in one go, from a list of 'CURRENT' and 'NEW' names

[novabond]

hello,

I am hoping that someone (much smarter than me!) has code to share, which will allow me to automatically change multiply tables names, throughout a workbook, in one go, based on information that I would enter/list in a worksheet within that workbook, under header cells say:
A1 (original table name)
B1 (new table name)
I have managed to find code that will magic a worksheet with all the current table names listed, I now need the code which will rename them.... and because there are so many, I need the criteria to be from ranges within a worksheet.

In addition, apart from the usual find/replace tool, is there a way I can automatically update formulas, within a worksheet or entire workbook, which reference tables whereby the names have changed, so that they update too.... in order for the formulas to still work?

Thank you in advance!

Best
BNOVA

 

[novabond]

again thank you so much!... i can't believe how wonderful and quickly these codes were shared and run!.. wherever you are in the world, who ever you are, you have just saved me so much time, that i can now spend with my toddler.... simply wonderful....

the thing is now you got me thinking, I should perhaps have mentioned, that i also have a load of 'NAMED RANGES' in the file too, that i would like be able to change the names of too, using the same principle.... is there any way the code could be adjusted to do this, so I have two macros that I can use to change names.... one for Tables and one for Named Ranges

or actually, should I be marking this post as solved and putting the Named Ranges addition as a new post entirely?, which hopefully you will find! apologies, I'm new to this?

just so fabulous...

 

[Akuini]

the thing is now you got me thinking, I should perhaps have mentioned, that i also have a load of 'NAMED RANGES' in the file too, that i would like be able to change the names of too, using the same principle.... is there any way the code could be adjusted to do this, so I have two macros that I can use to change names.... one for Tables and one for Named Ranges

or actually, should I be marking this post as solved and putting the Named Ranges addition as a new post entirely?, which hopefully you will find! apologies, I'm new to this?

It's a different problem, so you need to start a new thread.

 

[igold]

thank you so much!... the code is wonderful and works perfectly... so perfectly infract, it made me think of something... see my NAMED RANGE addition next in this feed...

You're welcome. We were both happy to help. Thanks for the feedback!

 

[igold]

I missed the part about replacing the old list of Table names with the new list of Table names. See if this does that for you...

Sub findtbls()

    Dim arr, arr2
    Dim wsCN As Worksheet: Set wsCN = Worksheets("ChangeName")
    Dim wss As Object, ws As Worksheet
    Dim tbls As Long, tbl As Long, i As Long, lRow As Long
    
    lRow = wsCN.Cells(Rows.Count, 2).End(xlUp).Row
    arr = wsCN.Range("A1:B" & lRow)
    arr2 = wsCN.Range("B1:B" & lRow)
    ReDim Preserve arr2(1 To lRow, 1 To 2)
    Set wss = Worksheets
    For Each ws In wss
        If Not ws.ListObjects.Count = 0 Then
            For tbl = 1 To ws.ListObjects.Count
                For i = 1 To UBound(arr)
                    If ws.ListObjects(tbl).Name = arr(i, 1) Then
                        ws.ListObjects(tbl).Name = arr(i, 2)
                    End If
                Next
            Next
        End If
    Next
    wsCN.Range("A1").Resize(UBound(arr2, 1), 2) = arr2
    MsgBox "Operation Complete"
    
End Sub