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:
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.
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
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.
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
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.
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
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.
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:
If you need to copy sheets to a different workbook, drag and drop won't work for you. Try this instead: