Find and rename Table in Workbook if it is present

[vanhunk]

Check if Table is existing in workbook and change its name if it is:

I need to determine with vba if a specific Table name, e.g. "ZA_Table", is already used in the workbook, if it is, I want the code to change the name of the existing Table to "OldTable".

1) How can this be done?
2) How can I determine what the full address of the table is, i.e. sheet name and range?

Range("ZA_Table").Address

only gives the range.

Thank you,
Kind Regards
vanhunk

[mana]

Option Explicit

Sub test()
    Dim ws As Worksheet
    Dim t As ListObject
    
    On Error Resume Next
    
    For Each ws In Worksheets
        Set t = ws.ListObjects("ZA_Table")
        If Not t Is Nothing Then
            t.Name = "OldTable"
            MsgBox t.Range.Address(external:=True)
            Exit For
        End If
    Next
    
End Sub

[vanhunk]

@mana:
Thank you very much, your code does the job and you also gave me another idea. Please have a look at the code below (also working fine).

Sub toets2()
Dim Adres As String

On Error Resume Next
    Adres = Range("ZA_Table").Address(external:=True)

    Range(Adres).ListObject.Name = "OldTable"

MsgBox Adres
End Sub

Regards
vanhunk

[vanhunk]

Add some checking for existing table and error checking. If the code is adding a table and the table already exists, it just adds a number to it, without warning or informing you. If you reference the new table with the name you gave it, it wont work because of the additional number at the end.

Regards,
vanhunk

[Aussiebear]

Maybe try this

Option Explicit
Sub test()
Dim ws As Worksheet 
Dim ws As Worksheet
Dim t As ListObject 
Dim t As ListObject
On Error Resume Next 
For Each ws In Worksheets 
Set t = ws.ListObjects("ZA_Table")
Set r = ws,ListObjects("OldTable")
If Not t Is Nothing Then
If Not R is Nothing Then
MsgBox "OldTable exists!" VBOkOnly
Exit Sub
Else
t.Name = "OldTable"
MsgBox t.Range.Address (external=:True)
Exit For
End If
End If
End Sub

[/CODE]

[snb]

Sub M_snb()
    If [isref(ZA_Table)] Then ListObjects("ZA_Table").Name = "old_table"
End Sub

[vanhunk]

Thank you VBAX Guru and snb, I appreciate your inputs!

Regards,
vanhunk