Home > Macros > Basics > Tables

Excel Table Macros

Debra Dalgleish - Contextures

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

    

Tip: If you need a macro to copy and paste selected table rows, go to the Copy Excel Table Rows page.

select a table to see sheet name and table address

1) List All Tables in Workbook

The following macros 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.

first macro lists a number for the SourceType property

second macro has a text description of the SourceType property

    

Tech Tip: You can find more info on SourceType on the Microsoft website.

1a) Table Info with SourceType Number

Copy the following VBA macro code to a regular code module, then run the macro when needed..

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

1b) Table Info with SourceType Description

Copy the following VBA macro code to a regular code module, then run the macro when needed.

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

2) Fix Problem: Table Does Not Expand

If Excel tables are not expanding automatically on your computer, there are two Excel Table AutoFormat settings that you can turn on or off:

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

Warning: Changing these settings will affect all workbooks that you open in Excel, on your computer. They are application-level settings.

2.1) Manually Change AutoFormat Settings

First, this short video shows how to change these table settings manually, and there are detailed steps on the Excel Tables Fix Expand page.

Or, use the two macros in the next section, turn the two Excel Table AutoFormat settings on or off.

2.2) Macro: Turn AutoFormat Settings On

This macro will turn the two Excel Table AutoFormat settings ON.

Copy the following VBA macro code to a regular code module, then run the macro when needed.

Sub ListAutoSetOn()

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

End Sub

2.3) Macro: Turn AutoFormat Settings Off

This macro will turn the two Excel Table AutoFormat settings OFF.

Copy the following VBA macro code to a regular code module, then run the macro when needed.

Sub ListAutoSetOff()

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

End Sub

3) 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:

  • The 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)

To address those issues, Alex used these settings in these macros:

  • the table name is hard coded (OrderRef)
  • the sheet name is not hard coded

3.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.

select a table to see sheet name and table address

Here is the Change event procedure on the TblInfo sheet, which runs automatically after you select a table name:

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, which the event procedure (above) runs.

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

3.2) Testing Sheet

The sample file also has two macros that can be tested with the buttons on the Testing worksheet. These two macros show how the target table and its sheet can be changed, even when their workbook is not active.

  • The TableChanges macro makes a change to the table style, after a new workbook has been created
  • The TableSheetChanges macro makes a change to the table style, and a worksheet cell, after a new workbook has been created.

You can get both macros when you download the sample workbook.

select a table to table sheet name testing macros

4) 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

Excel Named Tables

Create & Fix Excel Tables

Excel Table Auto Expand

Table Name, Show on Worksheet

Excel Table Slicers

Macro - Copy Excel Table Rows

 

 


Last updated: May 18, 2024 1:41 PM