refer to a table in VBA?

[merlin777]

Can you use a table in VBA rather than a specific range?

at the moment I'm using this:
.Range("B7:B23")

but I'd like to use a table so it could be added to by the user.

Is this possible and if so, what is the syntax please?

Thanks!

 

[alansidman]

Look at this link Referencing Tables | Excel 2013 VBA and Macros: Referring to Ranges | Que

 

[tonyyy]

One answer is...

ActiveSheet.ListObjects("Table1").Range

For an in depth look at Tables and vba, see...
[h=1]The VBA Guide To ListObject Excel Tables[/h]
Cheers,

tonyyy

 

[merlin777]

I used:

.Range("channelstable")
and it worked fine. How does that differ from what you specified?

One answer is...

ActiveSheet.ListObjects("Table1").Range

For an in depth look at Tables and vba, see...
The VBA Guide To ListObject Excel Tables


Cheers,

tonyyy

 

[tonyyy]

How does that differ from what you specified?

See the excerpt from Excel Tables...

There are two basic methods for referencing Tables and parts of Tables from VBA. The easiest is to use the same syntax as described above for formulas where you pass the Table information as a text string to the <code>Range</code> object. The other method is to use the Excel object models <code>ListObject</code> and its child properties and methods. Both are described below.

Using Range and Evaluate

Just as with standard referencing, the <code>Range</code> object evaluates structured references passed as a text string. For example, to reference the body or data portion of “Column 3” in the Table named “TableName” using the <code>Range</code> method:

<code>Range("TableName[Column 3]")</code>

Note that no qualifying worksheet is required because the table name has to be unique across all worksheets. This is the referencing style you will see when using the Macro Recorder.

Using the Object Model’s ListObject

To reference any part of a Table using the Excel object model, we have to first identify the Table object itself. The <code>ListObject</code> object is how Excel exposes a Table in the Excel object model. It is contained in the collection <code>ListObjects</code> which is a child of the <code>Worksheet</code> object. Use this syntax to reference a specific Table on a worksheet:

<code>ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")</code>

<code>ListObjects</code>, being a collection of list objects or Tables, can also be accessed with an index number:

<code>ThisWorkbook.Worksheets("Sheet1").ListObjects(1)</code>

The index number of the Table is the order in which it was created on the worksheet and is a read-only property.

 

[merlin777]

is there a way of not using the table name if it's the only table on the sheet?

I'm wanting to copy the table to another workbook and I won't know the name of the table (there will be 53 sheets and I will be able to use the sheet name but won't know the table name). Each table is identical and starts in the same cell on each sheet.

Would it be this?:

<code style="margin: 0px; padding: 0px; font-style: italic; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(242, 246, 248);">ThisWorkbook.Worksheets("week_1").ListObjects(1)</code>

I'll be using a cell to specify which week number's sheet to copy the table from and a cell for the workbook so I'm thinking this might work:

Dim t As String
i = Worksheets("sheet3").Range("a1")t = Worksheets("sheet3").Range("a2")
Workbooks(t).Worksheets("week_" & i).Activate
<code style="margin: 0px; padding: 0px; font-style: italic; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(242, 246, 248);">ListObjects(1).select</code>
Selection.Copy
Windows("newworkbook.xlsm").Activate
range("b1").activate
Worksheets("sheet1").Paste
End Sub

where sheet3 a1 is the week number and a2 contains the filename of the sheet. The table should be pasted into newworkbook.xlsx into sheet1.

As you can see, I'm still new at VBA! Does this look like it might work?

 

[merlin777]

there's a typo - this row:

i = Worksheets("sheet3").Range("a1")t = Worksheets("sheet3").Range("a2")

is split:


i = Worksheets("sheet3").Range("a1")
t = Worksheets("sheet3").Range("a2")</pre>

 

[Zack Barresse]

Hi there,

I know this is a couple months old, but I didn't see an answer. The answer to your question is yes, you can reference a Table by its index number. This is more complex than first glance, but easy if it's the only Table on a worksheet. It would go something like this...

Dim Table as ListObject
Set Table = Workbooks(Worksheets("sheet3").Range("a2").Value).Worksheets("weeks_" & Worksheets("sheet3").Range("a1").Value).ListObjects(1)
'... do stuff with Table ...

To note, there are a lot of potential points of failure in the above line. I would separate them out, like you did with the two variables.

 

[merlin777]

Hi there,

I know this is a couple months old, but I didn't see an answer. The answer to your question is yes, you can reference a Table by its index number. This is more complex than first glance, but easy if it's the only Table on a worksheet. It would go something like this...

Dim Table as ListObject
Set Table = Workbooks(Worksheets("sheet3").Range("a2").Value).Worksheets("weeks_" & Worksheets("sheet3").Range("a1").Value).ListObjects(1)
'... do stuff with Table ...

To note, there are a lot of potential points of failure in the above line. I would separate them out, like you did with the two variables.

cheers Zack.