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