refer to a table in VBA?

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647

ADVERTISEMENT

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.
 
Last edited:

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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:

Code:
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

Well-known Member
Joined
Aug 29, 2009
Messages
1,388

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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...

Code:
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

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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...

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,952
Messages
5,525,851
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top