Jump to another cell on a different sheet

[tjtsantos]

Hi Guys,

Please help me. I hope you can picture this one.

I have a "Master" sheet which cell A1 named "Chapter1"; then I inserted a new sheet which has the same name "Chapter1". I can create a hyperlink from Master!A1 to go to sheet Chapter1!A1. My question is, what code can I use on Chapter1!A1 to go back to the cell that has "Chapter1" on the "Master" sheet (in my example has to be Master!A1).

Basically, I need a common code to check the name of the active sheet and use that to search a cell from the "Master" sheet that has the same name.

Thank you very much!

Tony

[GTO]

When you say 'I have a "Master" sheet which cell A1 named "Chapter1"; ', are you saying that cell A1 now has the range name of 'Chapter1', or do you mean that the hyperlink displays this text?

Also, as you are putting hyperlinks on the first sheet, are you looking to insert hyperlinks on the other sheets, or a command button, or?

Mark

[tjtsantos]

Hi Mark,

Thanks, they are just cell values. Let me put it this way

Sheet1=Master
---Cell A1=Chapter1
---Cell B1=Chapter2
---Cell C1=Chapter3
Sheet2=Chapter1 -> has link go back to Sheet1!A1
Sheet3=Chapter2 -> has link go back to Sheet1!B1
Sheet4=Chapter3 -> has link go back to Sheet1!C1

I can just use hyperlink to jump from Sheet1 to the appropriate sheet. However, I would like to have a go back button on each sheet that will jump to Sheet1!A1, Sheet1!B1 accordingly. If I use hyperlink to go back, i will have problems when I insert in-between columns on sheet1.

NEW!

Sheet1=Master
---Cell A1=Chapter1
---Cell B1=Chapter1.5
---Cell C1=Chapter2
---Cell D1=Chapter3
Sheet2=Chapter1 -> has link go back to Sheet1!A1 (correct)
Sheet5=Chapter1.5 -> will have link to go back to Sheet1!B1
Sheet3=Chapter2 -> still has link go back to Sheet1!B1 (wrong!)
Sheet4=Chapter3 -> still has link go back to Sheet1!C1 (wrong!)

So I am looking forward to using a common code, the will use the name of the sheet to search the cells of sheet1 and land on the cell which has the same value as the sheet name. From above example I have to edit each hyperlink. We are talking about 50+ columns witch each corresponding sheets.

I hope this helps illustrate the problem.

[GTO]

Okay - though you mention sheets 2,3,4 "has link to go back..." you do say that you want a button on ea sheet, so take a look at this and the attached workbook. Does this help?

Option Explicit
Dim intShtNumber As Integer
Public Sub FallBack()
intShtNumber = CInt(Right(ActiveSheet.Name, Len(ActiveSheet.Name) _
- (Len(ActiveSheet.Name) - 1)))
Application.Goto Sheet1.Cells(intShtNumber, 1)
End Sub

[GTO]

OOPS! If you're going to have ten or more chapters, the previous won't work, as it only takes the last digit. Assuming all sheets will be named "Chapter" & (sheet number), you can change to:



intShtNumber = CInt(Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 7))


where 7 is with no space between the word "chapter" and the number, or 8 if you include the space.

Sorry about that...

[tjtsantos]

Hi Mark,

I checked on the file. It works fine, however, when I move Chapter2!A1 on cellC1 and insert a column before column A, the Go Back button still lands on the previous cells. How can it be modified so that it finds the marked cell wherever it may be on sheet1? We are getting there, thank you very much!

Tony

[GTO]

Tony,

You may insert columns to the left of column 1 ("A") and this should work.

Option Explicit
Dim intShtNumber As Integer, _
intColNum As Integer

Public Sub FallBack()

intShtNumber = CInt(Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 7))
intColNum = Sheet1.Cells.Find(What:="Chapter1!A1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Application.Goto Sheet1.Cells(intShtNumber, intColNum)
End Sub


I would note however that this will not work if you insert rows above the hyperlink locations. Is that a concern?

Mark

[tjtsantos]

Thanks Mark, I tested it and it worked fine. I inserted a row and it lands on the right cell. Perfect!

No, it is not a concern at all. They are fixed in a particular row.

I noticed in the code that it finds "Chapter1!A1"; Can we replace it with something generic like the ActiveSheet's Name instead? Instead of searching Chapter1!A1 specifically, it just match the activesheet's name and search it on the "master" sheet. This would mean using just one code. This is because when I use the same code to the button of Chapter2 sheet, it still lands on the Master!A2 instead of Master!B1 (all links are affixed on row1)

How can I attach or send you the excel I am trying to work on? Thanks a lot!

Tony

[tjtsantos]

Hi Mark,

Please see my attached file. This should describe the problem.

Thanks!

Tony

[GTO]

Hi Tony,

Hope you still need this. Anyways, you had both a command button and a hyperlink on ea of the "chapter" sheets, and the code supplied for the command buttons was missing. Thus - it looked like you are more interested in hyperlinks, so this is what I came up with.

As hyperlinks targets' (least as to when the target is a cell) don't self-adjust (as you noted), you could use the workbook event SheetActivate.

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim hlnk As Hyperlink, _
strSubAddress As String

If Sh.Name Like "Chapter*" Then
strSubAddress = "Master!" & _
shtvMaster.Rows("1:1").Find( _
What:=ActiveSheet.Name, After:=shtvMaster.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False _
).Address(True, True, xlA1)
For Each hlnk In ActiveSheet.Hyperlinks
If hlnk.Name = "Go Back" Then
hlnk.SubAddress = strSubAddress
End If
Next
End If
End Sub


In short, this searches all of row one and updates the target (SubAddress) of the hyperlink of the active 'chapter' sheet to the location of the cell/range location of the hyperlink on your 'master' sheet.

I would note that it only searches row one, as you specified that this is where all the hyperlinks on the first sheet will be.

Hopefully this helps.

Mark

[tjtsantos]

Hi Mark,

I just got back. I checked on it and it works! :clap:

Thanks a lot for helping me!!! I appreciate it very much. If I can be of help in any way, let me know.

Thanks again,

Tony