CODEWITHSUNDEEP
excelvbaexcel-formuladynamic-tables
Matt
Matt

Reputation: 25

dynamically renaming excel table per sheet and forwarding the dynamic name to other modules & macros

following setting

1 worksheet with raw-data 1 worksheet per week (e.g. week 31, 32, ...)

the raw data gets copied with an user action the current worksheet. however since the table name already exists in another week, table name "tableweek" the next copy of the worksheet gets its table renamed "tableweek2", "tableweek3" etc. since this is unpredictable I'd prefer to name the table same as a variable of the worksheet so I can use the table name in further macros and pivot.

however with all the topics about dynamically renaming table names I couldn't figure out how to correctly set it and hand it over to other macros (in other modules)

the best I've found is

Sub RenameTable()
With ActiveSheet
    .ListObjects(1).Name = "MyTableName"
End With

End Sub

however that is just manual when i combine it with a dynamic name (e.g. formula from a cell) I don't get it to work. furthermore how to hand it over to all other macros.

thanks for any help or pointing directions.

Upvotes: 0

Views: 4482

Answers (1)

Matt
Matt

Reputation: 25

Seems I wasn't that far from the solution:

Sub RenameTable()
    Dim tblName As String
    tblName = Range("D8").Text

    With ActiveSheet
        .ListObjects(1).Name = tblName
    End With
End Sub

Now I just need to hand over that "tblName" and that should be it.
Sharing it in case someone else likes that "solution"

Upvotes: 1

Related Questions