Excel Table Macros Use these macros to list all the Excel tables in a workbook, get sheet names where tables are stored, fix problem - table does not expand automatically

List All Tables in Workbook

The following macro will list all the named Excel Tables in the active workbook. A new worksheet is inserted, and the table name, sheet name, table address, and source type are listed.

• The first macro lists a number for the SourceType property
• The second macro has a text description of the SourceType property

See more info on SourceType on the Microsoft website

Table Info with SourceType Number

Sub ListTables()
Dim ws As Worksheet
Dim Lst As ListObject
Dim wsL As Worksheet
Dim lCount As Long
''Source Type: 0 external; 1 Range;
''  2 XML; 3 Query; 4 PowerPivot Model

lCount = 1

Set wsL = Worksheets.Add
With wsL.Range("A1:E1")
  .Value = Array("ID", "Table", "Location", _
      "Sheet", "Source Type")
  lCount = lCount + 1
End With

For Each ws In ActiveWorkbook.Worksheets
If ws.ListObjects.Count > 0 Then
  For Each Lst In ws.ListObjects
    wsL.Cells(lCount, 1).Value = lCount
    wsL.Cells(lCount, 2).Value = Lst.Name
    wsL.Cells(lCount, 3).Value = Lst.Range.Address
    wsL.Cells(lCount, 4).Value = ws.Name
    wsL.Cells(lCount, 5).Value = Lst.SourceType
    lCount = lCount + 1
  Next Lst
End If
Next ws

End Sub

Table Info with SourceType Description

Sub ListTablesSource()
Dim ws As Worksheet
Dim Lst As ListObject
Dim wsL As Worksheet
Dim lCount As Long
Dim strST As String

lCount = 1

Set wsL = Worksheets.Add
With wsL.Range("A1:E1")
  .Value = Array("ID", "Table", "Location", _
      "Sheet", "Source Type")
  lCount = lCount + 1
End With

For Each ws In ActiveWorkbook.Worksheets
If ws.ListObjects.Count > 0 Then
  For Each Lst In ws.ListObjects
    Select Case Lst.SourceType
      Case 0: strST = "External"
      Case 1: strST = "Range"
      Case 2: strST = "XML"
      Case 3: strST = "Query"
      Case 4: strST = "PowerPivot Model"
      Case Else: strST = "Unknown"
    End Select
    wsL.Cells(lCount, 1).Value = lCount
    wsL.Cells(lCount, 2).Value = Lst.Name
    wsL.Cells(lCount, 3).Value = Lst.Range.Address
    wsL.Cells(lCount, 4).Value = ws.Name
    wsL.Cells(lCount, 5).Value = strST
    lCount = lCount + 1
  Next Lst
End If
Next ws

End Sub

Sheet Name Macros

Thanks to Alex Blakenburg, who shared the following code for getting the sheet name for a specific Excel table. Alex needed code to make changes to a named Excel table, and to its worksheet, and there were a few things to consider:

• Macro would be run from a different workbook, not the file with the table
• Table's workbook might not be the active workbook - other workbooks were being created and edited
• Someone might have changed the sheet name (it's unlikely that the table name would be changed)

So, these macros have the table name hard coded (OrderRef), but not the sheet name.

1. Table Info

In the sample file, the TblInfo sheet has a drop down list, where you can choose a table name. That triggers a Change event procedure, which clears a couple of cells, and runs the following macro - ShowTableInfo.

• The macro fills in the Sheet Name and Table Address cells.
• A HYPERLINK formula in cell D2 lets you go to the selected table.

Here is the Change event procedure on the TblInfo sheet:

Private Sub Worksheet_Change _
  (ByVal Target As Range)
If Target.Address = _
    Range("SelTbl").Address Then
  Range("TblSh").ClearContents
  Range("TblAd").ClearContents
  If Target.Value <> "" Then
    ShowTableInfo
  End If
End If
End Sub

And here is the ShowTableInfo macro

Sub ShowTableInfo()
'by Alex Blakenburg
'select table name in SelTable cell
' table's sheet and address
'   added to cells below

Dim wb As Workbook
Dim shTI As Worksheet
Dim shData As Worksheet
Dim SelTable As Range
Dim TblSheet As Range
Dim TblAddr As Range
Dim rngTbl As Range
Dim tblData As ListObject
Dim tblName As String
Dim tblFullName As String

Set wb = ActiveWorkbook
Set shTI = wb.Sheets("TblInfo")
Set SelTable = shTI.Range("SelTbl")
Set TblSheet = shTI.Range("TblSh")
Set TblAddr = shTI.Range("TblAd")

tblName = SelTable.Value
If tblName = "" Then Exit Sub

tblFullName = "'" & wb.Name _
    & "'!" & tblName

Set rngTbl = Range(tblFullName)
Set shData = _
  wb.Sheets(rngTbl.Parent.Name)
Set tblData = _
  shData.ListObjects(tblName)

TblSheet.Value = shData.Name
TblAddr.Value = _
  tblData.Range.Address

End Sub

2. Testing Sheet

The next two macros can be tested with the buttons on the Testing worksheet. They show how the target table and its sheet can be changed, even when their workbook is not active.

TableChanges

This macro makes a change to the table style, after a new workbook has been created.

Sub TableChanges()
'by Alex Blakenburg
'use this technique to work
'  with table on unknown sheet
'table's workbook must be active
'  when tblData variable is set
Dim wb As Workbook
Dim tblData As ListObject
Dim tblName As String
Dim tblFullName As String

Set wb = ActiveWorkbook
tblName = "OrdersRef"

tblFullName = "'" & wb.Name _
    & "'!" & tblName
Set tblData = _
  Range(tblFullName).ListObject
'new workbook created,
'to show table can
' be changed if its
'workbook is not active
Workbooks.Add

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle
'if table is light blue (9)
'change to light green (14)
If tblData.TableStyle = _
    "TableStyleLight9" Then
tblData.TableStyle = _
    "TableStyleLight14"
Else
tblData.TableStyle = _
    "TableStyleLight9"
End If

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle

End Sub

TableSheetChanges

This macro makes a change to the table style, and a worksheet cell, after a new workbook has been created.

Sub TableSheetChanges()
'by Alex Blakenburg
'use this technique to work with
' sheet and table on unknown sheet
'table's wkbk must be active when
' sheet & table variables are set
Dim wb As Workbook
Dim shData As Worksheet
Dim rngTbl As Range
Dim tblName As String
Dim tblFullName As String
Dim tblData As ListObject

Set wb = ActiveWorkbook
tblName = "OrdersRef"

tblFullName = "'" & _
  wb.Name & "'!" & tblName
Set rngTbl = Range(tblFullName)
Set shData = _
  wb.Sheets(rngTbl.Parent.Name)
Set tblData = _
  shData.ListObjects(tblName)

'new workbook created, to show
' table and sheet can be changed
' even if workbook not active
Workbooks.Add

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle
'if table is light blue (9)
'change to light green (14)
If tblData.TableStyle = _
    "TableStyleLight9" Then
tblData.TableStyle = _
    "TableStyleLight14"
Else
tblData.TableStyle = _
    "TableStyleLight9"
End If

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle

With shData.Range("G1").Interior
  If .ColorIndex = 6 Then
    .ColorIndex = 4
  Else
    .ColorIndex = 6
  End If
End With

End Sub

Fix Problem: Table Does Not Expand

If Excel tables are not expanding automatically on your computer, use the following macro to turn on these two Excel Table AutoFormat settings:

• Include new rows and columns in table
• Fill formulas in tables to create calculated columns

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

Turn AutoFormat Settings On

To turn the Excel Table AutoFormat settings ON, use the following macro.

Note: If you prefer to change these settings manually, there are steps on the Excel Tables page.

Sub ListAutoSetOn()

With Application.AutoCorrect
  .AutoExpandListRange = True
  .AutoFillFormulasInLists = True
End With

End Sub

To turn the Excel Table AutoFormat settings OFF, use the following macro:

Sub ListAutoSetOff()

With Application.AutoCorrect
  .AutoExpandListRange = False
  .AutoFillFormulasInLists = False
End With

End Sub

Video: Table AutoFormat Settings

Watch the video, to see how to manually change the Excel Table AutoFormat settings. There are written steps and screen shots on the Excel Tables page.

Get the Sample File

1. Macros: To download the sample file with the tables and macros for this tutorial, click here: Table Files With Macros. The zipped file is in xlsm format, and DOES contain macros

Related Links

Create & Fix Excel Tables

Table Name, Show on Worksheet

Excel Table Slicers

Macro - Copy Excel Table Rows