Linking Cells with Hyperlinked Entries?

I have a workbook with multiple worksheets. I just realized that the task of maintaining what I'm building would be a lot easier if I just linked all of the data to a MasterWorksheet and conducted maintenance/editing on that sheet.

However,two columns in the MasterWorksheet contain hyperlinked fields and that hyperlink does not copy over when I link that cell on another sheet.... is there a workaround for this?

If a Marco is the solution, all the other sheets have duplicate data of the MasterWorksheet; the only difference is all the ChildWorksheets have filters applied to the data in a variety of ways.
LVL 1
-PolakAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
I do want to note that if all the following sheets are exactly the same as the first, then just making a copy of the first sheet and renaming, then adding filters is the way to go - no code at all.  However, with the links in place this allows you to make a change on the master sheet.  Given that changes could be made, I'll replicate the hyperlinks in columns E and F to the slave sheet.  That way, if you make a change, the hyperlinks are replicated.  It doesn't matter what type you use, I'll just copy them over.

This code copies columns E:F from the master to all worksheets that are not named "Master".  

Here's the code:

Option Explicit

Sub propagateHyperlinks()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksMaster As Worksheet

    Set wkb = ThisWorkbook
    Set wksMaster = wkb.Worksheets("Master")
    
    For Each wks In wkb.Worksheets
        If wks.Name <> "Master" Then
            wksMaster.Range("E:F").Copy
            wks.Range("E1").PasteSpecial
            Application.CutCopyMode = False
        End If
    Next wks
    
End Sub

Open in new window


See attached.  I've put a button to run this from the master sheet.

PS - If you want to replicate the entire sheet from column A:F (since they all have the same data) and not worry about links at all then you can just change lines 13:14 to:

            wksMaster.Range("A:F").Copy
            wks.Range("A1").PasteSpecial

Open in new window




Cheers,

Dave
CurriculumEESafe-r1.xlsm
0
 
dlmilleCommented:
Can you provide a non-sensitive version of your workbook to work from?

Thanks,

Dave
0
 
-PolakAuthor Commented:
Sure, here you go. All links have been set to google.com.
CurriculumEESafe.xlsx
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dlmilleConnect With a Mentor Commented:
You don't need a VBA macro, necessarily, unless what we come up with on a formula basis drains computer speed, then we can use a macro to generate the embedded links as you have.

I believe your first step should be to add HYPERLINK formulas to your first sheet, then on the copy over, you'll have to use a hyperlink formula there, as well.

I've done this on the first 5 rows for you to see the example.

The HYPERLINK function is entered as follows, for internet links:

=HYPERLINK("http://www.google.com","Friendly Name - E.g., Business Writing for Results")

So, when you link over on other sheets you'll need to just copy/paste the HYPERLINK formula over, otherwise, you can add another column to then reference the base link with the hyperlink formula.  In either case, linking to a  hyperlink doesn't make a new hyperlink.  You either have to copy/paste the link from the initial page (embedded) or the formula should be copied over or built linking to a REFERENCE cell - E.g., Hyperlink(reference!A2,"Friendly Name") or Hyperlink(Reference!A2,Reference!A3).

I've created those helper columns in the main sheet and report sheet, for the first 5 rows and used the HYPERLINK formula to generate the results you desire.

See attached,

Dave
CurriculumEESafe.xlsx
0
 
-PolakAuthor Commented:
I don't hate this solution; but, I'm a little confused by what you meant by:
"unless what we come up with on a formula basis drains computer speed, then we can use a macro to generate the embedded links as you have".

In my mind, since all of the cells are in the same position on each worksheet (just filtered differently) couldn't a macro just run to say if not blank copy cells in columns E and F to columns E and F on all subsequent worksheets?  

In my opinion that's a more elegant solution then the helper columns and having to Hide/Unhide them for publication/subseqent maintenance.
0
 
dlmilleCommented:
Please advise if you want to use the HYPERLINK formula on the master sheet, or you want embedded hyperlinks.  Its easy enough to copy/paste the result on the successive sheets.

Dave
0
 
-PolakAuthor Commented:
Thanks. Works great even if run once the other sheets are already filtered.

Thank you for showing me the non-VBA way of doing it; I'll let you know which I decide on, but I think the Macro is the cleaner solution.
0
 
-PolakAuthor Commented:
Excellent Expert. Provided two complete solutions to answer my question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.