enter a hyperlink in one cell on a worksheet and it copy to other worksheets

bpolkusm

New Member
Joined
Nov 20, 2017
Messages
12
Hello Everyone,

I have a workbook and on the Summary tab worksheet in Cell C41 I have a hyperlink to a website. The workbook has 111 worksheets The website changes for each client for each workbook. I am looking for a formula or something that when I update the hyperlink on Summary Tab in Cell C41 it copies that hyperlink as a active link to Cell E10 on the other work sheets.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,157
Office Version
  1. 2013
Platform
  1. Windows
Are you saying here. Your quote:
The website changes for each client for each workbook.
Do you mean the Hyperlink will not be the same in each worksheet?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,157
Office Version
  1. 2013
Platform
  1. Windows
If your wanting to modify the Excel Hyperlink in Range("C41") of the Summary Tab
and this same link will be entered in all sheets in your workbook Range("E10")

Put this script in the summary sheet:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Summary sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$41" Then
'Modified  4/5/2021  10:54:36 PM  EDT
Dim i As Long
Dim ans As String
ans = ActiveSheet.Name

For i = 1 To Sheets.Count

    With Sheets(i)
        Select Case Sheets(i).Name
        Case ans
            Case Else
                Target.Copy Sheets(i).Range("E10")
        End Select
    End With
Next
End If
End Sub
 

bpolkusm

New Member
Joined
Nov 20, 2017
Messages
12
Are you saying here. Your quote:
The website changes for each client for each workbook.
Do you mean the Hyperlink will not be the same in each worksheet?
The hyperlink will stay the same for each worksheet with in each workbook. I am trying to prevent the client from having to go all the way back to the summary tab to get to the link and my team not to have to update 111 worksheets with the same website.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,157
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The hyperlink will stay the same for each worksheet with in each workbook. I am trying to prevent the client from having to go all the way back to the summary tab to get to the link and my team not to have to update 111 worksheets with the same website.
So did the script I provided do what you want?
If not please explain more.
 

Forum statistics

Threads
1,147,748
Messages
5,742,971
Members
423,769
Latest member
LongToast

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