+ Reply to Thread
Results 1 to 3 of 3

Jump to other worksheets in a workbook based on cells in a worksheet in the same workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    02-05-2015
    Location
    Burnie, Tasmania
    MS-Off Ver
    2010
    Posts
    2

    Jump to other worksheets in a workbook based on cells in a worksheet in the same workbook

    Hi,
    I have a workbook with many worksheets. In the first sheet there are numbers in each cell of column 2. Numbers will be added to this column as necessary. The numbers have been used to name each of the other worksheets. Each of the numbers has been allocated to a worksheet. I want to be able to double click on a number in the first worksheet to jump to the worksheet which is named by that corresponding number.

    I'm sure this can't be that hard - tried this but it only works on the first cell and target sheet. Any ideas?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    v = Target.Value
    For Each sh In Worksheets
    If sh.Name = v Then
    Cancel = True
    sh.Activate
    End If
    Next
    End Sub

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: Jump to other worksheets in a workbook based on cells in a worksheet in the same workb

    Hello GillianR and welcome to the forum,

    If the numbers are in column 2 (B), then this code should work:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim v As String
        Dim sh As Worksheet
        
        If Target.Count > 1 Then Exit Sub
        If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
        v = Target.Value
        For Each sh In Worksheets
            If sh.Name = v Then
                sh.Activate
            End If
        Next
        Cancel = True
    End Sub
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    02-05-2015
    Location
    Burnie, Tasmania
    MS-Off Ver
    2010
    Posts
    2

    Re: Jump to other worksheets in a workbook based on cells in a worksheet in the same workb

    Thanks GC Excel, Works perfectly

+ 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] Copy data from 2 cells in one worksheet to the same cells in all worksheets in a workbook
    By daveb86 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-16-2014, 03:37 PM
  2. [SOLVED] Copying ranges of cells from different worksheets in a workbook based on an if then
    By stinkybottom in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-13-2012, 09:19 PM
  3. Copy multiple worksheets from 2003 workbook to one worksheet in 2010 workbook
    By KeithRoberts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-06-2012, 10:18 AM
  4. jump to specific date in cell-range based on last opening of workbook
    By wamp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-20-2008, 09:06 AM
  5. Replies: 4
    Last Post: 06-24-2005, 02:05 PM

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