extracting or replacing table names

[Frank.Egelund.Jensen]

I have a big workbook with approx 100 sheets. In each sheet I have two tables, and as I manually entered all the data, I just copied the sheet layout, so now I have funny table names like RHO925_stamdata1012223064666870727682.

I would like to replace these names.

I have found a way to change the table name, but it requires that I know the current name.

How can I extract the current Table name or replace it without know the current Table name ?

 

[lhartono]

Try this, it will auto rename the table to "Tablexxx".

[COLOR=#0000cd]Sub AutoTableRename()
[/COLOR][COLOR=#d3d3d3]'by lhartono[/COLOR][COLOR=#0000cd]
Dim wsh     As Worksheet
Dim tbl     As ListObject
Dim idx     As Long

For Each wsh In ActiveWorkbook.Sheets
    For Each tbl In wsh.ListObjects
        idx = idx + 1
[/COLOR][COLOR=#d3d3d3]        'Debug.Print wsh.Name & vbTab & tbl.Name & vbTab & ("Table" & Format(idx, "000"))[/COLOR][COLOR=#0000cd]
        tbl.Name = ("Table" & Format(idx, "000"))
    Next
Next

End Sub[/COLOR]

 

[Frank.Egelund.Jensen]

Thank a lot - you solved my problems. Have a Nice day.

Try this, it will auto rename the table to "Tablexxx".

[COLOR=#0000cd]Sub AutoTableRename()
[/COLOR][COLOR=#d3d3d3]'by lhartono[/COLOR][COLOR=#0000cd]
Dim wsh     As Worksheet
Dim tbl     As ListObject
Dim idx     As Long

For Each wsh In ActiveWorkbook.Sheets
    For Each tbl In wsh.ListObjects
        idx = idx + 1
[/COLOR][COLOR=#d3d3d3]        'Debug.Print wsh.Name & vbTab & tbl.Name & vbTab & ("Table" & Format(idx, "000"))[/COLOR][COLOR=#0000cd]
        tbl.Name = ("Table" & Format(idx, "000"))
    Next
Next

End Sub[/COLOR]