List Excel defined Tables in a workbook [VBA]
The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers in the active workbook.
Table of Contents
1. Macro - VBA code
'Name macro Sub ListTables() 'Declare variables and data types Dim tbl As ListObject Dim WS As Worksheet Dim i As Single, j As Single 'Insert new worksheet and save to object WS Set WS = Sheets.Add 'Save 1 to variable i i = 1 'Go through each worksheet in the worksheets object collection For Each WS In Worksheets 'Go through all Excel defined Tables located in the current WS worksheet object For Each tbl In WS.ListObjects 'Save Excel defined Table name to cell in column A Range("A1").Cells(i, 1).Value = tbl.Name 'Iterate through columns in Excel defined Table For j = 1 To tbl.Range.Columns.Count 'Save header name to cell next to table name Range("A1").Cells(i, j + 1).Value = tbl.Range.Cells(1, j) 'Continue with next column Next j 'Add 1 to variable i i = i + 1 'Continue with next Excel defined Table Next tbl 'Continue with next worksheet Next WS 'Exit macro End Sub
2. List Excel Tables - example
Sheet1, 2 and 3 contain three tables.
3. Run macro
- Go to "Developer" tab
- Press with left mouse button on "Macros" button
- Press with left mouse button on "ListTables"
- Press with left mouse button on "Run"
4. Where to copy macro
- Press Alt+F11 to open the VB Editor
- Press with left mouse button on "Insert" on the menu and then press with left mouse button on module.
- Copy above VBA code and paste to code module, see image above.
5. Get file
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
3 Responses to “List Excel defined Tables in a workbook [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
That's really handy, thanks!
Thanks for this. Is there a way to list the table numbers (instead of the headers) next to the table names ?
Gav,
table numbers? Do you mean all the data in the table?