+ Reply to Thread
Results 1 to 3 of 3

Find links to current active worksheet

Hybrid View

  1. #1
    Registered User
    Join Date
    06-20-2014
    Location
    Houston, Texas
    MS-Off Ver
    Office 365 2013
    Posts
    7

    Find links to current active worksheet

    Hello, fellow excel nerds.

    I am trying to get a macro to work (again) that is designed to find all sheets in a workbook that have formulas that are linking to the current active sheet. I found this solution online and it actually worked when I first got it. I created an XLAM to have it on hand, but now when I run it, the output is reversed.

    Instead of a message box that showing other tabs that link to the current sheet, it now shows the sheets that formulas on the current tab are linking to.

    The bizarre thing is that it did work when I first used it, but now it doesn't.. Tried recopying etc. but is not functioning. Any ideas?

    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

  2. #2
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Find links to current active worksheet

    Hi,

    No need for a dictionary here. Here is the modified code:
    Sub ShowLinks()
    ''==============================================
    ''Find formulas that reference other sheets, and
    ''display a list of referenced sheets
    ''==============================================
    'DECLARE AND SET VARIABLES
    Dim rng As Range, x As String, SheetRef As String
    On Error Resume Next
    '-----------------------------------------------
    'CYCLE THROUGH ALL SHEETS
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If .Name <> ActiveSheet.Name Then 'IF NOT THE ACTIVE SHEET THEN
                Set rng = .Cells.SpecialCells(xlCellTypeFormulas)
                If Err.Number <> 0 Then Err.Clear:: GoTo nextsheet 'NO FORMULAS FOUND
    '-----------------------------------------------
    'TEST IF FOUND FORMULA REFERS TO THE ACTIVE SHEET
                For Each c In rng 'FORMULAS FOUND
                    If InStr(1, c.Formula, "!") > 0 Then
                        x = Split(c.Formula, "!")(0)
                        x = Right(x, Len(x) - 1)
                        If x = ActiveSheet.Name Then 'REFERS TO THE ACTIVE SHEET
    '-----------------------------------------------
    'BUILD LIST OF SHEETS THAT HAVE FORMULAS THAT REFERENCE THE ACTIVE SHEET
                            If SheetRef = "" Then
                                SheetRef = Worksheets(I).Name
                            Else
                                SheetRef = SheetRef & Chr(13) & Worksheets(I).Name
                            End If
                            GoTo nextsheet
                        End If
                    End If
                Next c
            End If
        End With
    nextsheet:
    Next I
    '-----------------------------------------------
    'DISPLAY LIST OF SHEETS
     MsgBox "The following sheets have links the the worksheet named " & ActiveSheet.Name & _
                       Chr(13) & SheetRef
    '-----------------------------------------------
    'CLEANUP
        Set rng = Nothing
    End Sub
    HTH,
    Maud

    form1.png
    Last edited by Maudibe; 05-01-2018 at 07:21 PM.

  3. #3
    Registered User
    Join Date
    06-20-2014
    Location
    Houston, Texas
    MS-Off Ver
    Office 365 2013
    Posts
    7

    Re: Find links to current active worksheet

    Awesome!! Thank you very much! Works like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Macro copying to whatever the current active worksheet is.
    By ZEROCOOL UK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2017, 11:22 AM
  2. How to find out the current active cell formula?
    By alexduy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2015, 05:52 PM
  3. [SOLVED] copy and paste in current active cell, and need current date then scroll down 140 lines
    By vengatvj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2013, 03:40 AM
  4. How to Make Current Open Worksheet as Active
    By momer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2013, 12:45 PM
  5. Current cell position for a non Active worksheet
    By Wizz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-15-2009, 08:40 PM
  6. Find Active worksheet after Workbooks.Opentext???
    By windyweather in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2007, 06:52 PM
  7. [SOLVED] How to indicate that the current visible worksheet is the active o
    By George Furnell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2006, 01:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1