How to use VBA code to find references in multi worksheet Excel workbook

While working on a complex Excel workbook that has several worksheets with lots of formulas, logic, and pivot tables, it can become a challenge to make changes without breaking something. The following VBA code examples help you find references across all worksheets in an Excel Workbook, making it easy to find affected rows and columns and make educated decisions.

Table of Contents

Overview

The code examples shared in this blog are as below:


Find Table References

The code example below will help you find references for a specific table. First, find the table name in the "Table Design" menu in Excel.

Sub FindTableReferences()
    Dim ws As Worksheet
    Dim cell As Range
    Dim tableRef As String
    Dim found As Boolean

    ' Set the table name you are looking for
    Dim tableName As String
    tableName = "YourTableName"

    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If InStr(cell.Formula, tableName) > 0 Then
                Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
                found = True
            End If
        Next cell
    Next ws

    If Not found Then
        MsgBox "No references found for table: " & tableName
    End If
End Sub

Find Sheet References

The code example below will help you find references for a specific sheet.

Sub FindSheetReferences()
    Dim ws As Worksheet
    Dim cell As Range
    Dim sheetRef As String
    Dim found As Boolean

    ' Set the sheet name you are looking for
    Dim sheetName As String
    sheetName = "YourSheetName"

    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If InStr(cell.Formula, sheetName) > 0 Then
                Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
                found = True
            End If
        Next cell
    Next ws

    If Not found Then
        MsgBox "No references found for sheet: " & sheetName
    End If
End Sub

Find Columns in Table

The code example below will help you find references for a specific column.

Sub FindColumnInTables()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim found As Boolean
    Dim columnName As String

    ' Set the column name you are searching for
    columnName = "YourColumnNameHere"

    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            For i = 1 To lo.ListColumns.Count
                If lo.ListColumns(i).Name = columnName Then
                    Debug.Print ws.Name & ": Table " & lo.Name & ", Column " & columnName
                    found = True
                End If
            Next i
        Next lo
    Next ws

    If Not found Then
        MsgBox "No columns found with name: " & columnName
    End If
End Sub

Find References in Workbook

The code example below will help you find references to a specific text in a workbook.

Sub FindReferencesInWorkbook()
    Dim ws As Worksheet
    Dim cell As Range
    Dim found As Boolean
    Dim reference As String
    Dim outputMessage As String

    ' Set the reference you are searching for
    reference = "YourTextHere"

    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If InStr(cell.Formula, reference) > 0 Then
                Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
                found = True
            End If
        Next cell
    Next ws

    If Not found Then
        MsgBox "No references found for: " & reference
    End If
End Sub

Conclusion

We have covered four unique ways to make your life simpler while working in Excel. These functions can make editing your Excel very easy and foolproof. Even if you are trying to study an Excel workbook shared by someone else, or digging into an old Excel file to find references, the above functions can come in handy.

We're Social!

Email us or follow us on LinkedIn, GitHub, or X.