Dynamic Table Names - Possible?

[ramohse]

Hello


I am wondering if there is a clever way to automatically make a table's name the same as whatever the name of the sheet is on which it resides.

Basically I want to create a table on a sheet named S01W01, then ctrl+copy that sheet and name the new, copied sheet S01W02, and have the table on the first sheet be named "S01W01" and the table on the second named "S01W02" (for dynamic dashboard purposes). I could use VBA to accomplish this (maybe on a sheet-copy event or something?), but I plan on handing this file off to people who are not familiar with VBA and would prefer they be able to copy sheets at will and have everything work.

Can this happen? I am using Excel 2010.

Thank you!

 

[theBardd]

You could try adding this to ThisWorkbook code module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)Const TABLE_NAME_BASE As String = "S01W"


    With ThisWorkbook


        On Error Resume Next
        Sh.ListObjects(1).Name = TABLE_NAME_BASE & Format(Sh.Index, "00")
    End With
End Sub

 

[ramohse]

Thank you! For some reason it doesn't like the Sub title. It is red in the VBA editor, and the names haven't changed...

 

[ramohse]

haha nevermind, I got it. Works perfectly! Thank you very much! And that was exceedingly clever...