rename A LOT of tables on several worksheets

[dsause]

I have several worksheets with dozens (...even hundreds) of tables and none of them were named, so i have a bunch of tables with names like "Table4858333454" .
I need no rename them all based on the worksheet they are in, and the name in the A1 (top left header) section of the table. AND some tables have more than one word, "Something Healthcare Partner" .. so there's spaces too

There is also two tables (side-by-side) for each 'Something Healthcare Partners'... So i have two tables that need distinction
[image bellow]


Worksheet name: 'West Coast Analytics'
Table top left header: 'Something Healthcare Partners'

What I'd like the rename each table to: "West_Coast_a1_Something_Healthcare_Partners"
(and for the one next to it "West_Coast_a2_Something_Healthcare_Partners")

I am pretty new to VBA and know I will have to use it for this

Can I get help as to where to start or what the solution may look like?

Thanks

 

[gigidica]

Try this code:

Dim i, N As Integer
N = 9999 'N being the number of tables
For i = 1 To N 'loop through worksheets
Sheets(i).Select
Tname = ActiveCell.ListObject.Name 'Assign current table name to a variable
Qname = ActiveSheet.Name 'Assign current worksheet name to a variable
Sname = ActiveSheet.Range("A1") 'Assign the info in cell A1 to a variable
ActiveSheet.ListObjects(ans).Name = Qname & "_" & Sname 'Rename the table
Next i

Let me know if this helps you

 

[gigidica]

sorry....small typo in the above code:

Dim i, N As Integer
N = 9999 'N being the number of tables
For i = 1 To N 'loop through worksheets
Sheets(i).Select
Tname = ActiveCell.ListObject.Name 'Assign current table name to a variable
Qname = ActiveSheet.Name 'Assign current worksheet name to a variable
Sname = ActiveSheet.Range("A1") 'Assign the info in cell A1 to a variable
ActiveSheet.ListObjects(Tname).Name = Qname & "_" & Sname 'Rename the table
Next i