Which worksheets link to the active worksheet?

When you copy a worksheet, it's useful to know whether that worksheet contains formulas that reference other sheets. It is also useful to know which sheets are referenced. There are a couple of good reasons for this:

If two or three worksheets form a self-contained module (eg: a detail sheet, an executive summary and a pivot table) you will find that copying all three sheets together will maintain the references. You can rename the sheets after copying and it should just work -- but you need to copy them at the same time to achieve this.
You will save time correcting the bad references, either using the Replace command to point the links to a different sheet, or using the Edit > Links feature if you have copied the sheet to another workbook.
Should you decide that you want to delete a worksheet, you need to know that nothing else depends on it. Otherwise you will end up with #REF! errors scattered through your workbook.

This tutorial shows VBA routine that will tell you which pages reference the current page; if you don't know how to copy two or more worksheets simultaneously, see this tip at the bottom of the page.

Finding which worksheets link to the current sheet

Transfer this code to a new module (Alt+F11, Insert>Module, Paste this code, then Alt+Q to return to Excel).

To run the code press Alt+F8 and double-click the macro name. Once the code has run you will see a message box. If it is blank, no sheets link to the current sheet. Otherwise you will see a list of the sheets that do link to the current sheet.

Sub ShowLinks()
''==============================================
''Find formulas that reference other sheets, and
''display a list of referenced sheets
''==============================================
    Dim Rng As Range, _
        c As Range
    Dim dic As Object, _
        dic2 As Object
    Dim x, y, z
    Dim j As Long, _
        k As Long, _
        m As Long
    Dim Sht As Worksheet
    Dim strSheets As String
    
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic2 = CreateObject("Scripting.Dictionary")
    Set Rng = Cells.SpecialCells(xlCellTypeFormulas)
    j = 0
    For Each c In Rng
        If InStr(1, c.Formula, "!") > 0 Then 'references another sheet
        'load all unique strings into a Dictionary object
            x = Split(c.Formula, "!")
            If Not dic.exists(x(0)) Then
                j = j + 1
                dic.Add x(0), j
            End If
        End If
    Next c
    If j=0 Then 'no formulas with links
        MsgBox "This sheet is not linked to other sheets", vbInformation
        GoTo ExitHere
    End If
    y = dic.keys
    'Now we have a list of unique strings containing sheet names
    'referenced from this sheet. Next step is to list just the sheet names.
    m = 0
    For k = LBound(y) To UBound(y)
        For Each Sht In ActiveWorkbook.Worksheets
            If InStr(1, y(k), Sht.Name) > 1 Then
                If Not dic2.exists(Sht.Name) Then
                    m = m + 1
                    dic2.Add Sht.Name, m
                End If
                Exit For
            End If
        Next Sht
    Next k
    strSheets = Join(dic2.keys, vbCrLf)
    MsgBox strSheets

ExitHere:
    Set dic2 = Nothing
    Set dic = Nothing
    Set Rng = Nothing
End Sub

How does it work?

The code uses the Dictionary object, a special kind of array with two dimensions. The first dimension is a series of unique keys; these are the text values that you want to retrieve later. The second dimension is an index, usually built by incrementing numbers. What makes the Dictionary so useful is that the keys will always be unique, so duplicates are not an issue; and retrieving data from a Dictionary is fast.

There are three main steps to the code.

Harvesting formulas with links

Once all the variables are declared, the cells to be searched are restricted to those containing formulas. Then, each formula is check for the presence of an exclamation mark (indicating a link to another sheet).

Using the Split function we take everything to the left of the exclamation mark (Split generates a zero-based array, so x(0) is the first element in the array) and check to see whether or not it is already in the Dictionary. If not, we add it to the dictionary along with an index number

    Set Rng = Cells.SpecialCells(xlCellTypeFormulas)
    j = 0
    For Each c In Rng
        If InStr(1, c.Formula, "!") > 0 Then 'references another sheet
        'load all unique strings into a Dictionary object
            x = Split(c.Formula, "!")
            If Not dic.exists(x(0)) Then
                j = j + 1
                dic.Add x(0), j
            End If
        End If
    Next c

Stopping if no links were found

The routine may find no formulas with links. If that is the case, there is no point continuing. The snippet below checks to see if the counter (j) is still 0; if it is, a message displays and the code jumps to the exit point.

    If j=0 Then 'no formulas with links
        MsgBox "This sheet is not linked to other sheets", vbInformation
        GoTo ExitHere
    End If

You could use Exit Sub instead of the GoTo statement but I chose the latter, so that the code can clean up the object references before quitting.

Building a list of sheet names referenced in the formula list

Now, we extract all of the keys from the Dictionary object into a new array. Each item in the array is searched for the names of sheets in the workbook. As before, it is added to the Dictionary only if the sheet name is not already there.

    y = dic.keys
    'Now we have a list of unique strings containing sheet names
    'referenced from this sheet. Next step is to list just the sheet names.
    m = 0
    For k = LBound(y) To UBound(y)
        For Each Sht In ActiveWorkbook.Worksheets
            If InStr(1, y(k), Sht.Name) > 1 Then
                If Not dic2.exists(Sht.Name) Then
                    m = m + 1
                    dic2.Add Sht.Name, m
                End If
                Exit For
            End If
        Next Sht
    Next k

Displaying the result

Finally, two lines of code create the MsgBox that displays the result. Just as Split was used to break a string into chunks, Join can be used to concatenate them; here we use vbCrLf to place each item on a new line, then display the result in a MsgBox.

    strSheets = Join(dic2.keys, vbCrLf)
    MsgBox strSheets

The last few lines set the object variables to Nothing, to free up the memory that was allocated to them. The code is fairly straightforward and demonstrates the use of the Split and Join functions, and the Dictionary object. I hope that you find it useful.

Duplicating several sheets at once

When you duplicate one or more worksheets using this method, you will have an exact duplicate of the sheet, including any custom row heights and column widths. It's really simple:

Copying within the same workbook

Select the sheet tab while holding down the Ctrl key
Still holding the Ctrl key, drag the tab sideways and let go
If the original sheet was called Template, you will now have a copy called Template (2), which you can rename
To duplicate several sheets, select the tabs that you want to duplicate while holding down the Ctrl key, then drag and let go.

Copying to a different workbook

If you need to copy sheets to a different workbook, drag and drop won't work for you. Try this instead:

Select the tab(s) as detailed above.
Right-click one of the tabs and select Move or Copy.
In the dialog, choose the file to copy the sheets to. You can elect to make a copy (check the box if you need to), or just move the sheets; this will remove them from the original workbook and transfer them to the new workbook.

DataWright Information Services

Consulting and Resources for Excel and Access