VBA code to reference dynamically changing Table name

[spydey]

I am kind of stumped on this one.

I have a single worksheet that has a number of different tables.

It also has a number of different formulas that reference data inside of those tables.

I had a problem where if I combined multiple workbooks, all of which contained the same tables, then my formulas would be incorrect. Reason being that tables of the same name get auto-renamed when combined into the same workbook.

So I thought I had a workaround for that.

The workbook/worksheet is a template and I have to put in a unique number in cell A1. The number will never be repeated amongst all the workbooks/worksheets, even when combined.

I figured, I will just auto-rename all the tables to contain the unique number, that way the formulas auto-update, the named ranges auto-update, and the tables will be unique, even when multiple worksheets are combined into a single workbook.

So, when I input a number in cell A1, using worksheet_change, I have all tables be renamed to tblname_number.

Example: I input 552843 in cell A1. The table name changes from "Fruit" to "Fruit_552843". The table "Veggies" changes to "Veggies_552843". The table "Food" changes to "Food_552843". etc etc etc.......

The code works great!!

And, when I clear out cell A1, then the tables auto-rename back to being "Fruit", "Veggies", "Food", etc......

Love it!!

However, I have several lines of code, modules, etc., that reference the exact table name and column in the table.
Because they are lines of code with a hard coded table name, when the table name auto-changes based upon a number input, the lines of code no longer function correctly.

Example:

ws.Range("Fruit[Apples]")

That line of code will not auto update to correctly reference the auto-named tables.

Question: Is there a way to reference a dynamic table name in a hard line of code? This is where I am a bit shaky.

I was thinking something like: ws.Range("Fruit & "_" & ws.Range("A1").value[Apples]")

So that basically it would give me: ws.Range("Fruit_12345"[Apples]")

I realize that the syntax probably isn't correct, but I think it portrays the idea clearly .... right?

My only concern about doing that is, if I clear out cell A1 from a previously input number, then it would reference table "Fruit_", when the real name of the table (when there is no number in cell A1) is "Fruit". So the line of code would still be broken.

I guess that I could correct my auto-rename of the tables to not include the underscore ("_"), so that it would be "Fruit12345".

But then how do I identify where to remove the numbers from the original table name when I clear out cell A1, so that it reverts back to "Fruit"?
I am currently using SPLIT and using the underscore as the separator.

Any thoughts, ideas, etc?

-Spydey

 

[spydey]

Revisiting this issue ... hoping that someone has an idea to my dilemma, or a pointer, or suggestion .... something ...

I thought about inputting an IF statement, but that would still leave me where I am at, in that I need to combine a range with some text to make a new range. That text is based off of the value of a cell.

-Spydey

 

[John_w]

Along the lines you suggested, maybe:

    Dim ws As Worksheet
    Dim tableColumn As String
    
    Set ws = Worksheets("Sheet1") 'use correct sheet name
        
    tableColumn = "Fruit"
    If ws.Range("A1").Value <> "" Then tableColumn = tableColumn & "_" & ws.Range("A1").Value
    tableColumn = tableColumn & "[Apples]"
    Debug.Print ws.Range(tableColumn).Address

 

[Fazza]

Just throwing the idea out there, any chance of naming the tables like wbkname_tablename

 

[Yongle]

I assume that when workbooks are "combined" entire sheets including any tables are copied across

If there is only one table on each sheet then that table can be referred to like this

Sheets("SheetXXX")[COLOR=#b22222].ListObjects(1)[/COLOR]

instead of this

    Range("Fruit[[#Headers],[Apples]]")

can use this

    Range(Sheets("SheetXXX").ListObjects(1).Name & "[[#Headers],[Apples]]")

Multiple tables on a sheet can be constently referred to in code with .ListObjects(1) , .ListObjects(2) etc

BIG CAVEAT - The above referencing method works for multiple tables if those TABLES are STATIC within a sheet (cannot be moving, inserting or deleting tables otherwise the table index number is liable to change)

I am not a fan of structured references - I prefer the more user-friendy special ranges illustrated below

Sub TableRefs()
[COLOR=#b22222]    Dim tbl_1 As ListObject[/COLOR]
    Dim tbl_All As Range, tbl_HdrRow As Range, tbl_Data As Range
    Dim tbl_Col_1 As Range, tbl_Data_Col_1 As Range, tbl_Row_2 As Range, tbl_Hdr_2 As Range
    Dim tbl_Any_Cell As Range, tbl_Totals As Range
        
    Set tbl_1 = [COLOR=#b22222]ActiveSheet.ListObjects(1)[/COLOR]
    
    With tbl_1
[I][COLOR=#006400]    'table, data only, headers & totals[/COLOR][/I]
        Set tbl_All = .Range                                    'table including headers
        Set tbl_Data = .DataBodyRange                           'table data only
        Set tbl_HdrRow = .HeaderRowRange                        'header row
        Set tbl_Totals = .TotalsRowRange                        'totals row (if box checked)
[COLOR=#006400][I]    'specific rows, columns and cells etc[/I][/COLOR]
        Set tbl_Hdr_2 = .HeaderRowRange(2)                      '2nd header
        Set tbl_Col_1 = .ListColumns(1).Range                   '1st column
        Set tbl_Data_Col_1 = .ListColumns(1).DataBodyRange      '1st column data only
        Set tbl_Row_2 = .ListRows(2).Range                      '2nd row
        Set tbl_Any_Cell = .DataBodyRange(2, 4)                 'cell in data row 2 column 4
        
    End With

[I][COLOR=#006400]'or create structured references as below 
'here selecting Header cell entitled "Apples" 
[/COLOR][/I][I][COLOR=#006400]'4 ways to write the same instruction[/COLOR][/I]
    Range(tbl_1.Name & "[[#Headers],[Apples]]").Select
    Range("Fruit[[#Headers],[Apples]]").Select
    Range(ActiveSheet.ListObjects(1).Name & "[[#Headers],[Apples]]").Select
    tbl_1.HeaderRowRange(2).Select                              '(where "Apples" is the 2nd header )
End Sub