link to the 1st sheet to be applied on multiple worksheets.

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Have to put a link "back to beginning" in 300 worksheets at the top, 1 cell location. So far it woks only with text, but not with links.

Then here is more difficult the same thing "back to beginning" at the bottom, but the length of the sheets varies.
+Have to put the link "to the top" at the end of the info on the sheet, that leads to the top of the same page.

Thank you.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here is VBA code to "Back to Beginning in all worksheets in cell B1 assuming Beginning is named Sheet1 and cell A1

Option Explicit

VBA Code:
Sub Hyper()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("B1"), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="Back To Beginning"
        End If
    Next ws
    MsgBox ("Completed")
End Sub

Placing same in Last cell in Column A of worksheet

VBA Code:
Option Explicit

Sub Hyper()
    Dim ws As Worksheet
    Dim lr As Long
    

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="return to sheet1"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Error in my second code. Missed a plus one. See below for updated Code.

VBA Code:
Option Explicit

Sub Hyper()
    Dim ws As Worksheet
    Dim lr As Long
    

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr + 1), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="Back To Beginning"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Error in my second code. Missed a plus one. See below for updated Code.

VBA Code:
Option Explicit

Sub Hyper()
    Dim ws As Worksheet
    Dim lr As Long
   

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr + 1), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="Back To Beginning"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
Waw, great!!! Thank you.
May be know how to do the same with "to top", when it links to the top of the page?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

I don't understand what you want. Maybe you can explain it a little more clearly. Are you looking for a hyperlink that when clicked at the bottom of a sheet moves the cursor to Cell A1? Please clarify succinctly
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
I don't understand what you want. Maybe you can explain it a little more clearly. Are you looking for a hyperlink that when clicked at the bottom of a sheet moves the cursor to Cell A1? Please clarify succinctly
Yes, exactly. Normally we have located next to the back to beginning which is on the bottom.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Here is the code to add the hyperlink at the cell below the last cell in each sheet.

VBA Code:
Option Explicit


Sub HyperUp()
    Dim ws As Worksheet
    Dim lr As Long
    

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr + 1), _
              Address:="", _
              SubAddress:="'" & ws.Name & "'!A1", _
              TextToDisplay:="To The Top"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
G
Here is the code to add the hyperlink at the cell below the last cell in each sheet.

VBA Code:
Option Explicit


Sub HyperUp()
    Dim ws As Worksheet
    Dim lr As Long
   

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr + 1), _
              Address:="", _
              SubAddress:="'" & ws.Name & "'!A1", _
              TextToDisplay:="To The Top"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
Great!! You are the best!!!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks for the feedback.
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Here is VBA code to "Back to Beginning in all worksheets in cell B1 assuming Beginning is named Sheet1 and cell A1

Option Explicit

VBA Code:
Sub Hyper()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("B1"), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="Back To Beginning"
        End If
    Next ws
    MsgBox ("Completed")
End Sub

Placing same in Last cell in Column A of worksheet

VBA Code:
Option Explicit

Sub Hyper()
    Dim ws As Worksheet
    Dim lr As Long
   

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add _
              Anchor:=ws.Range("A" & lr), _
              Address:="", _
              SubAddress:="Sheet1!A1", _
              TextToDisplay:="return to sheet1"
        End If
    Next ws
    MsgBox ("Completed")
End Sub
Question, finally in a lot cases I have the text that covers several columns, but the link works when you get in the beginning. (example attached).
Is it possible to merge the cells for the length of the text?
 

Attachments

  • merge.png
    merge.png
    2.1 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top