Jump to a Particular Worksheet based on the current cell's value

[K m]

I would like to be able to have a macro that would look at the current cell and jump to the worksheet based on that cell's value plus 10. Perhaps something like the Indirect function.

Examples:
- say that the current cell's value is 12, jump to the 22nd worksheet. I do not want to jump to worksheet22 but the 22nd worksheet

- Another example, say the current cell's value is 24, jump to the 34th worksheet

[Winon]

Hi K m,

What would you expect to happen if you are on, say the 5th last sheet of the Workbook?

[K m]

The workbook I have has about 100 worksheets. The first sheet is also the Index sheet which shows me the names of all of the other sheets.

I currently have a macro

Sub GotoSheet()
  
  ' Jumping between Worksheets
    Dim sSheet As String
    sSheet = InputBox( _
      Prompt:="Sheet name or number?", _
      Title:="Input Sheet")
    On Error Resume Next
    If Val(sSheet) > 0 Then
        Worksheets(Val(sSheet)).Activate
    Else
        Worksheets(sSheet).Activate
    End If
End Sub

The macro prompts me to input the sheet number and jumps to that sheet. I'd like to change the macro to read the current cell contents and do a jump based on the cell contents

[Winon]

Hi K m,

I'd like to change the macro to read the current cell contents and do a jump based on the cell contents

Just to clarify. Do you want to select a Cell with a Sheet name on the Index Sheet, and then Click a Button to take you to that specific Sheet?

[K m]

Actually the Index Sheet is named Summary but yes that is what I want.

What I would like is to either click on a macro button or run the macro via a keystroke sequence ie Ctrl/Shift J.

I thought the macro I started above could be modified to work

[Winon]

Hi K m,

The attached WorkBook automatically creates your Summary list and adds Hyperlinks to all the Sheets in the WorkBook on the Summary Sheet. If you add a new Sheet just Click on the Create New Link Button on the Summary Sheet, and the new Sheet will be added with a Hyperlink.

Now simply click on a Link of your choice, and you are there. All the Sheets in this sample have a Link Back to Summary Sheet.

[K m]

I am including an example of the workbook with three worksheets. My workbook has over 100 worksheets.

Notes:
- I have highlighted in yellow the cells that are to be used to determine which sheet to be used for jumping
- There are several worksheets ahead of the Sorted_Summary worksheet (they are hidden) so although the first worksheet listed and reference number for Andy_Mountain is one it is not the first worksheet but the 11th worksheet. The same for the other worksheets 10 plus the number shown
=============
My macro works as long as I supply the correct number, I just want the macro to read the cell information, add 10 and jump to that worksheet number

[BeachRock]

I'm not sure if this will help but it sounds like what you are trying to accomplish is similar to what I did with something recently. The code below looks at a specific cell and hides or unhides specific sheets based on the value in a certain cell. I had to remove the rest through the 24th sheet due to the length of it in the thread but you get the idea.

Sub HideOtherSchedules()

Dim rng As Range, cell As Range
 Set rng = Intersect(Range("T3"), ActiveSheet.UsedRange)
 For Each cell In rng
 If (cell.Value) < 8 _
 Then
 Sheets("8TeamSched").Visible = False
 Sheets("9TeamSched").Visible = False
 Sheets("10TeamSched").Visible = False
 Sheets("11TeamSched").Visible = False
 Sheets("12TeamSched").Visible = False
 Sheets("13TeamSched").Visible = False
 Sheets("14TeamSched").Visible = False
 Sheets("15TeamSched").Visible = False
 Sheets("16TeamSched").Visible = False
 Sheets("17TeamSched").Visible = False
 Sheets("18TeamSched").Visible = False
 Sheets("19TeamSched").Visible = False
 Sheets("20TeamSched").Visible = False
 Sheets("21TeamSched").Visible = False
 Sheets("22TeamSched").Visible = False
 Sheets("23TeamSched").Visible = False
 Sheets("24TeamSched").Visible = False
   ElseIf (cell.Value) = 8 _
 Then
 Sheets("8TeamSched").Visible = True
 Sheets("9TeamSched").Visible = False
 Sheets("10TeamSched").Visible = False
 Sheets("11TeamSched").Visible = False
 Sheets("12TeamSched").Visible = False
 Sheets("13TeamSched").Visible = False
 Sheets("14TeamSched").Visible = False
 Sheets("15TeamSched").Visible = False
 Sheets("16TeamSched").Visible = False
 Sheets("17TeamSched").Visible = False
 Sheets("18TeamSched").Visible = False
 Sheets("19TeamSched").Visible = False
 Sheets("20TeamSched").Visible = False
 Sheets("21TeamSched").Visible = False
 Sheets("22TeamSched").Visible = False
 Sheets("23TeamSched").Visible = False
 Sheets("24TeamSched").Visible = False
 Sheets("8TeamSched").Select
  ElseIf (cell.Value) = 9 _
 Then
 Sheets("8TeamSched").Visible = False
 Sheets("9TeamSched").Visible = True
 Sheets("10TeamSched").Visible = False
 Sheets("11TeamSched").Visible = False
 Sheets("12TeamSched").Visible = False
 Sheets("13TeamSched").Visible = False
 Sheets("14TeamSched").Visible = False
 Sheets("15TeamSched").Visible = False
 Sheets("16TeamSched").Visible = False
 Sheets("17TeamSched").Visible = False
 Sheets("18TeamSched").Visible = False
 Sheets("19TeamSched").Visible = False
 Sheets("20TeamSched").Visible = False
 Sheets("21TeamSched").Visible = False
 Sheets("22TeamSched").Visible = False
 Sheets("23TeamSched").Visible = False
 Sheets("24TeamSched").Visible = False
 Sheets("9TeamSched").Select

 'I had to remove the rest through the 24th sheet due to the length of it in the thread but you get the idea.

 End If
 
 Next cell
  
End Sub

[K m]

Winon:

Your example overwrites what is in column A. Can this be done either with a macro or without destorying what is in column A ?

[Winon]

Could you please upload a sample of how your Workbook layout is structured?

[K m]

The hyperlink method does not work for my workbook. I suspect that is because my worksheet names are not named Sheet1, Sheet2, Sheet3, etc. but names like Ashville, Westbrook, etc.

[Winon]

Hi K m,

In Post No.6 I have said;

The attached WorkBook automatically creates your Summary list and adds Hyperlinks to all the Sheets in the WorkBook on the Summary Sheet. If you add a new Sheet just Click on the Create New Link Button on the Summary Sheet, and the new Sheet will be added with a Hyperlink.

It does not matter what you have Named your Sheets, the Code will Copy the Actual Sheet Name and not Sheet 1 or Sheet 4 etc.

Use my Sample WorkBook and Change it how you want and Click on the Add New Link Button. You will the see that it is actually "Perfect", in that with the HyperLink, you just click on the Sheet name you wish to go to.

[K m]

Tony:
I appreciate the input but I don't think this will work for me:
- Your example it appears it references a particular cell T3. I would like to reference the current cell location not cell T3.
- I have over 100 worksheets, writing code to hid and unhide over 100 worksheets is not what I am looking for.

I just want to jump to a particular worksheet based on current cell value

Thanks

[BeachRock]

Sorry, K m, I provided code that I use and was completely based on how I use it and not cpntaining your cell criteria. I just provided it as an example.

[K m]

I decided to use the Hyperlink approach that Winon suggested. Not exactly what I wanted but it works

[Winon]

Hi K m,

Not exactly what I wanted but it works

The attached WorkBook is as close as you would get without using HyperLinks.

The Sheet Names you have listed in Column B must reflect the exact Sheet Name.

In Cell C2 I am using a hidden Formula to validate a selection with a "Helper Column BA" which is automatically populated by the Code, everytime you hit the "Go" Button - just in case you have added or deleted some sheets.


Please have a look at it, and let me know what you think about this revised approach.

[K m]

Thank you. I will try this out.

Unfortunately for me Column B names are not identical to the sheet names. All of my sheet names of two words or more have an underscore between words. Column B does not use underscores.

The Hyperlink method works just fine. I didn't think of that

Thanks

Kirk