Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Linked Worksheets (2003)

    I am trying to get the following code to help me quickly identify the worksheets that link to the active sheet:

    Sub FindLinks()
    Let SourceSheet = ActiveSheet.Name
    Let FindWhat = "'" & SourceSheet & "'!"
    For Each w In ActiveWorkbook.Worksheets
    w.Activate
    On Error GoTo 1
    Cells.Find(What:=FindWhat, LookIn:=xlFormulas).Activate
    MsgBox "'" & w.Name & "' has links to '" & SourceSheet & "'", vbOKOnly, "Linked Worksheet Found"
    1 Next w
    End Sub

    The problem I am having is when the string cannot be found. It is producing an error code of 91 and not going to line 1 as I would like.

    Can anyone offer a solution?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Linked Worksheets (2003)

    Please turn on 'Require variable declaration' in the Editor tab of Tools | Options... in the Visual Basic Editor, and declare all variables you use in your code. This will save you a lot of grief in the future.

    Here is code that doesn't use On Error, but assigns the result of the Find method to a Range variable and tests if it is Nothing.

    Sub FindLinks()
    Dim SourceSheet As String
    Dim FindWhat As String
    Dim w As Worksheet
    Dim r As Range
    SourceSheet = ActiveSheet.Name
    FindWhat = "'" & SourceSheet & "'!"
    For Each w In ActiveWorkbook.Worksheets
    Set r = w.Cells.Find(What:=FindWhat, LookIn:=xlFormulas)
    If Not r Is Nothing Then
    MsgBox "'" & w.Name & "' has links to '" & SourceSheet & "'", vbOKOnly, "Linked Worksheet Found"
    End If
    Next w
    End Sub

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Linked Worksheets (2003)

    Hans,

    Many thanks for the speedy reply.

Posting Permissions

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