Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Is it possible to link pages in two separate workbooks

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is it possible to link pages in two separate workbooks

    Does anyone know if it is possible to link pages in multiple workbooks. I want to be able to click on a upc code in a cell and have it take me to the same upc code in a separate workbook. Does anyone know how to do this? Basically you would have to search by the upc code. I have done this in in different pages of an excel workbook, but never in two separate workbooks.

    I have provided the code I used below. If anyone knows a way to modify it to work for two separate workbooks please let me know. Thanks!

    Option Explicit

    Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim Found As Range

    Set Found = Worksheets("Sheet1").Cells.Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
    Cancel = True
    Else
    Cancel = False
    Worksheets("Sheet1").Activate
    Found.Select
    End If

    End Sub

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    519
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    You would just need to modify the Find function to reference the other workbook.
    Code:
    Set Found = Workbooks("SecondWorkbook.xlsx").Sheets(1).Cells.Find(What:=Target.Value, After:=Workbooks("SecondWorkbook.xlsx").Sheets(1).Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
    And, change the Activate to reference the workbook, as well.
    Code:
    Workbooks("SecondWorkbook.xlsx").Sheets(1).Found.Select
    Last edited by AFPathfinder; Dec 8th, 2017 at 03:29 PM.

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    Hi unfortunately I am still having trouble with the code. This is what I have been using. Can you see where I am running into trouble? Thanks so much!


    Option Explicit

    Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim Found As Range

    Set Found = Workbooks("SecondWorkbook.xlsx"). Sheets(1) .Cells.Find(What:=Target.Value,
    After: =Workbooks (“SecondWorkbook.xlsx”) . Sheets(1) .Cells (1, 1), _
    Lookin: xlValues, LookAt: =xlPart, SearchOrder: =xlByRows,
    SearchDirection: = xlPrevious, MatchCase: = True)
    If Found Is Nothing Then
    Cancel = True
    Else
    Cancel = False
    Workbooks("SecondWorkbook.xlsx").Sheets(1) .Found.Select
    Found.Select
    End If

    End Sub

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    519
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    What trouble are you running into? Besides the missing underscores in the Find function, I'm not sure what errors you are running into.
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    If you can get it into Excel, imagination is the usual limitation.

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    The macro will not run. I'm not sure what the problem is. Where should the underscores go? Would they keep the macro from running?

  6. #6
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    519
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    Give this version a shot:
    Code:
    Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim Found As Range
        
        On Error Resume Next
        Set Found = Workbooks("SecondWorkbook.xlsx").Sheets(1).Cells.Find(What:=Target.Value, _
            After:=Workbooks(“SecondWorkbook.xlsx”).Sheets(1).Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=True)
        On Error GoTo 0
            
        If Found Is Nothing Then
            Cancel = True
        Else
            Cancel = False
            Workbooks("SecondWorkbook.xlsx").Sheets(1).Found.Select
        End If
    End Sub
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    If you can get it into Excel, imagination is the usual limitation.

  7. #7
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    It says compile error variable not defined when I try to run the macro with that code. It also has the first row of code under option explicit highlighted. Any ideas?
    Thanks!

  8. #8
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    file:///C:/Users/order_000/Desktop/Macroerror.JPG

  9. #9
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    519
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    Try this one.
    Code:
    Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim Found As Range
        
        On Error Resume Next
        Set Found = Workbooks("SecondWorkbook.xlsx").Sheets(1).Cells.Find(What:=Target.Value, _
            After:=Workbooks(“SecondWorkbook.xlsx”).Sheets(1).Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=True)
        On Error GoTo 0
            
        If Not Found Is Nothing Then
            Found.Select
        End If
    End Sub
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    If you can get it into Excel, imagination is the usual limitation.

  10. #10
    New Member
    Join Date
    Dec 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it possible to link pages in two separate workbooks

    Unfortunately the same problem is happening. The same pop up box comes up and it highlights the same things.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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