Excel Table MacrosUse 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 |
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.
See more info on SourceType on the Microsoft website
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
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
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:
So, these macros have the table name hard coded (OrderRef), but not the sheet name.
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.
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
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
If Excel tables are not expanding automatically on your computer, use the following macro to turn on these two Excel Table AutoFormat settings:
NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.
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
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.
Last updated: November 23, 2022 7:50 PM