Rename Table using another Cell value

[RichSA]

Hi,

I am trying to rename a Table using a Macro. I want to rename the Table based on a my own text "CAT_" and cell value in the sheet.

Sub Rename_Table()
ReName = Range("C2").Value
ActiveSheet.ListObjects("Table1").Name = ("CAT_")&ReName
End Sub

Would appreciate some guidance.

 

[My Aswer Is This]

Try this:

Sub Name_Me()
'Modified 4/20/2021  3:23:10 AM  EDT
ActiveSheet.ListObjects("Table1").Name = "CAT_" & Range("C2").Value
End Sub

 

[RichSA]

Try this:

Sub Name_Me()
'Modified 4/20/2021  3:23:10 AM  EDT
ActiveSheet.ListObjects("Table1").Name = "CAT_" & Range("C2").Value
End Sub

Works perfectly, can't believe the simple change needed. Much appreciated.

 

[My Aswer Is This]

Works perfectly, can't believe the simple change needed. Much appreciated.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[RichSA]

Will certainly come back to Mr. Excel, spent 4 hours this morning trying to solve it and you come up with a simple answer just like that.

 

[novabond]

Try this:

Sub Name_Me()
'Modified 4/20/2021  3:23:10 AM  EDT
ActiveSheet.ListObjects("Table1").Name = "CAT_" & Range("C2").Value
End Sub

Hi RichSA

I wondered if you wouldn't mind letting me know, the modified code so that the original table name is also looked up from a cell, instead of it being hard set in the code, cell ("C1") say... This is because I have a project with a worksheet containing many tables, all of which I have to change the name of... (Ihave found the code to allow me to get the names exported to a worksheet)

OR

Ideally, and I have been searching all day, I was hoping for code which will allow me to automatically change multiply tables names, throughout a workbook, in one go, based on information that I would enter/list in a worksheet, under header cells say:
A1 (original table name)
B1 (new table name)
as mentioned above, I have found code that will enable me to get the original table names listed, now I just need a way changing them all, as painlessly as possible!

Many thanks in advance!

BNOVA

 

[novabond]

Hi RichSA

I wondered if you wouldn't mind letting me know, the modified code so that the original table name is also looked up from a cell, instead of it being hard set in the code, cell ("C1") say... This is because I have a project with a worksheet containing many tables, all of which I have to change the name of... (Ihave found the code to allow me to get the names exported to a worksheet)

OR

Ideally, and I have been searching all day, I was hoping for code which will allow me to automatically change multiply tables names, throughout a workbook, in one go, based on information that I would enter/list in a worksheet, under header cells say:
A1 (original table name)
B1 (new table name)
as mentioned above, I have found code that will enable me to get the original table names listed, now I just need a way changing them all, as painlessly as possible!

Many thanks in advance!

BNOVA

Try this:

Sub Name_Me()
'Modified 4/20/2021  3:23:10 AM  EDT
ActiveSheet.ListObjects("Table1").Name = "CAT_" & Range("C2").Value
End Sub

hey apologies, just realised i should have started with
Hi My Aswer is This!....

also, I meant to say, the tables are throughout a workbook, not worksheet.... TIA

 

[Fluff]

As this is totally different from the original question, you need to start a thread of your own. Thanks

 

[novabond]

ok thanks Fluff.... i didn't realise, will do...