Overview

While working on a complex Excel workbook that has several worksheets with lot of formulas, logic, pivot tables, …, it can become a challenge to make changes without breaking something or the other.

For that reason, I would like to share the below code examples in VBA that can be used to find references across all worksheets in an Excel Workbook. This makes it so easy to find out all affected rows and columns and make an educated decision to apply changes, as well as helps with testing the new changes.

The code examples shared in this blog are as below:

Find Table References
Find Sheet References
Find Columns in Tables
Find References in Workbook

Find Table References

The code example in this section will help you find references for a specific table. First of all how do you find out what is the table name of the worksheet. To find that, you go to the menu “Table Design” and in the first box you will see value for Table name as depicted in the screenshot below:

Next step is to go to the menu “developer” and click on “Visual Basic”. This will open another window, like in the screenshot below:

I have already a new module by right clicking and inserting a new module.

Now, on the right side of the module, you can copy and paste the below code:

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" ' Change this to your table's name
 
    ' Begin looping through each sheet
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                ' Check if the formula contains the table name
                If InStr(1, cell.Formula, tableName, vbTextCompare) > 0 Then
                    ' Print the cell address and its formula if it contains the table reference
                    Debug.Print "Found in " & ws.Name & " at " & cell.Address & ": " & cell.Formula
                    found = True
                End If
            End If
        Next cell
    Next ws
 
    ' If no references are found, notify the user
    If Not found Then
        MsgBox "No references to the table '" & tableName & "' were found in this workbook."
    Else
        MsgBox "References to the table '" & tableName & "' have been printed to the Immediate Window."
    End If
End Sub

In the above highlighted row, you can replace “YourTableName” with “ReportedTB” as in the example above to search for references. If in the whole workbook, it finds a reference for that table name, it will Print the cell address and its formula in the “Immediate” window upon execution.

Find Sheet References

The code example in this section will help you find references for a specific sheet.

Please go back to the “Visual Basic” window as described in the first section.

I have already a new module by right clicking and inserting a new module.

Now, on the right side of the module, you can copy and paste the below code:

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" ' Change this to your sheet's name
 
    ' Begin looping through each sheet
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                ' Check if the formula contains the sheet name
                If InStr(1, cell.Formula, sheetName & "!", vbTextCompare) > 0 Then
                    ' Print the cell address and its formula if it contains the sheet reference
                    Debug.Print "Found in " & ws.Name & " at " & cell.Address & ": " & cell.Formula
                    found = True
                End If
            End If
        Next cell
    Next ws
 
    ' If no references are found, notify the user
    If Not found Then
        MsgBox "No references to the sheet '" & sheetName & "' were found in this workbook."
    Else
        MsgBox "References to the sheet '" & sheetName & "' have been printed to the Immediate Window."
    End If
End Sub

In the above highlighted row, you can replace “YourSheetName” with “RepoZP” as in the example above to search for references. If in the whole workbook, it finds a reference for that sheet name, it will Print the cell address and its formula in the “Immediate” window upon execution.

Find Columns in Table

The code example in this section will help you find references for a specific column.

Please go back to the “Visual Basic” window as described in the first section.

I have already a new module by right clicking and inserting a new module.

Now, on the right side of the module, you can copy and paste the below code:

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"
    found = False
 
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each table on the worksheet
        For Each lo In ws.ListObjects
            ' Check each column in the table
            Dim col As ListColumn
            For Each col In lo.ListColumns
                If col.Name = columnName Then
                    ' If found, show a message and exit the loops
                    MsgBox "Column '" & columnName & "' found in table '" & lo.Name & "' on sheet '" & ws.Name & "'"
                    found = True
                    Exit For
                End If
            Next col
            If found Then Exit For
        Next lo
        If found Then Exit For
    Next ws
 
    ' If the column is not found in any table
    If Not found Then
        MsgBox "Column '" & columnName & "' not found in any table."
    End If
End Sub

In the above highlighted row, you can replace “YourColumnNameHere” with “Account” as in the example above to search for references. If in the whole workbook, it finds a reference for that column name, it will Print the column in table name information on which worksheet and cell address in the “Immediate” window upon execution.

Find References in Workbook

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

Please go back to the “Visual Basic” window as described in the first section.

I have already a new module by right clicking and inserting a new module.

Now, on the right side of the module, you can copy and paste the below code:

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"
    found = False
    outputMessage = "References found at: " & vbCrLf
 
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each cell in the worksheet
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                ' Check if the cell formula contains the reference
                If InStr(1, cell.Formula, reference, vbTextCompare) > 0 Then
                    ' Append the location to the output message
                    outputMessage = outputMessage & ws.Name & " - " & cell.Address & vbCrLf
                    found = True
                End If
            End If
        Next cell
    Next ws
 
    ' Print the result in the Immediate Window
    If found Then
        Debug.Print outputMessage
    Else
        Debug.Print "No references to " & reference & " found in any formulas."
    End If
End Sub

In the above highlighted row, you can replace ” YourTextHere ” with “‘Tab’!$A:$A” as an example above to search for references. If in the whole workbook, it finds a reference for that text, it will Print the worksheet name and cell address in the “Immediate” window upon execution.

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 fool proof. Even if you are trying to study an Excel workbook shared by someone else, or digging an old Excel to find references, the above functions can come in handy.

We hope you enjoyed this blog post. We will add more blogs to make your life simpler.

About the Author

Michael is a seasoned data professional with over 20 years of technology experience. He has worked on different databases, used multiple programming languages, and is passionate on transforming data into productive intelligence. Michael has worked with different data visualization tools and is currently working in Microsoft PowerBI space.

He is passionate to help customers solve their complex business problems and achieve maximum profitability. He strives to work on complex issues with a strong sense of ownership and urgency. He is a team player with excellent technical, collaboration, interpersonal, oral, and written communication skills. He has experience working in different verticals.

How to use VBA code to find reference(s) in multi worksheet Excel workbook

Table of Contents

Overview

Find Table References

Find Sheet References

Find Columns in Table

Find References in Workbook

Conclusion