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