Dynamically refer to a table name

[freelensia]

Hello everybody, I am joining the forum for the first time. Hope to learn a lot from everybody and exchange useful information.

With Excel 2013 we can use Tables just as named ranges.

I am trying to refer to the Table "Extract". However, this table name may change in the future. As such, I would like to register it once as a public variable then refer to the public variable name for the rest of the Module. An example is the following:

Public ExtractTable As Range

Sub ResizeExtract()
Set ExtractTable = Range("Extract")

Range(ExtractTable.Name&"[[#Headers],[username]]").ColumnWidth = 10.75

End Sub

However I get syntax error.

Could somebody help me? Thanks!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

If it were me, I would take the time to learn about ListObjects. This is what Tables used to be called in previous versions of Excel.

There is a whole object model based on ListObjects and it allows tou to manipulate Tables easily.

OK, the syntax gets a bit of getting used to but it is what I would do. Your question becomes:

Sub ResizeExtract()
    Dim ExtractTable As ListObject
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set ExtractTable = .ListObjects("Extract")
        ExtractTable.ListColumns("username").Range.ColumnWidth = 10.75
    End With
    
End Sub

For an introduction to what is possible you could do worse than look here: https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables



Rergards,