Worksheet Name: Belmont
Table 1 Name: Table811131517192125
Table 1 Header names:
Date
Median
Average
Sales
Pending
Inventory
DOI
SP/LP
DOM
MedSy
AveSy
SalesSy
PendSy
InvenSy
TableRange: $A$5:$U$87

Worksheet Name: Belmont
Table 2 Name: Table710121416182024
Table 2 Header names:
Median
Average
Sales
Pending
Inventory
DOI
SP/LP
DOM
MedSy
AveSy
SalesSy
PendSy
InvenSy
TableRange: $W$5:$AP$87

The current table names are useless for creating formulas, charts, etc... I
want the table names to be user friendly: BelmontSFR for the first table and
BelmontCT for the second table.

"OssieMac" wrote:

Having a real problem trying to interpret exactly what you want to do. Can
you answer the following for me.

Provide the following for one worksheet before any changes.

Worksheet name:
Table 1 name:
Table 1 header names:
Table 1 range:

Table 2 name:
Table 2 header names:
Table 2 range:

What do you want the above to be after the changes?


If having difficulty providing the above table details before changes then
run the following code on the active sheet.

Sub TableDetails()
Dim ws As Worksheet
Dim LstObj As ListObject
Set ws = ActiveSheet
For Each LstObj In ws.ListObjects
MsgBox "Table name: " & _
LstObj.Name & vbCrLf & _
"Table range: " & LstObj.Range.Address
Next
End Sub

--
Regards,

OssieMac


"rdcsfd" wrote:

I have over 100 similar worksheets with two tables on each sheet, currently
with excel inscrutable names. I would like to automatically change the names
of the tables to reflect two things:
1. the name of the worksheet
2. conatentate sfr on the first table in the sheet and ct to the second
table on the sheet.

I've spent the last four hours searching the internet and playing around
with this to no avail.

Recording a macro gives me this:
Range("Table.AthertonSFR[[#Headers],[Date]]").Select
ActiveSheet.ListObjects("Table811131517192123").Na me = "AthertonSFR"
Range("Table710121416182022[[#Headers],[Median]]").Select
ActiveSheet.ListObjects("Table710121416182022").Na me = "AthertonCT"

Which, as the names of all the tables are different, is useless, and I can't
figure out how to just select "Table1" then "Table2".

The second problem is the new name. The formula to generate the new name is
=(MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256))&"SFR"

When I test that portion, it returns "compile error: syntax error"

Any help would be greatly appreciated.

TIA