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
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
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
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
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.