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.
'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
Example
Sheet1, 2 and 3 contain three tables.
Run macro
- Go to "Developer" tab
- Click "Macros" button
- Click "ListTables"
- Click "Run"
Where to copy macro
- Press Alt+F11 to open the VB Editor
- Click "Insert" on the menu and then click on module.
- Copy above VBA code and paste to code module, see image above.
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
One Response 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!