DataWright Information Services

Consulting and Resources for Excel and Access




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.