VBA: Bulk Inserting Hyperlinks to Sheets with Same Name as Cell Value (Excel 2016 VBA)

[UnleashtheData]

Hi!

I’m hoping for a macro that can:

• Start in A36 of the active worksheet and continue downward, stopping when it reaches a blank cell (ideally, it could do this at once for all selected worksheets, if multiple are selected.)
• In each of those applicable cells, insert a hyperlink to the worksheet with the same name as the text in the cell.
• The values in the Column A cells should be left the same.

Also, it’s be great if I could get a second macro that would do the same thing as the first, except it would link to the worksheet with the same name as the concatenation of the A cell and the corresponding R cell. For example, the hyperlink in A36 would link to the worksheet with the name given by the formula: =CONCAT(A36,R36).

You may be wondering why I don’t just use the HYPERLINK function to add these links. I’ve tried the following formula starting in B36: =HYPERLINK("[" & MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) & "]" & A36 & "!A1",A36) . As I’m sure you can tell, the intention of a large part of this formula is to pull the workbook name for the hyperlink. But because Excel is pulling a Temporary Internet File path instead of the normal file path, the hyperlink does not work. Error message: “Cannot Open the specified file.” If you’d like to help me troubleshoot this that would be appreciated, though the VBA would probably be preferable since it allows for the insertion of hyperlinks in Column A, where there is already a formula.

Thank you so much for your time!!

 

[Perpa]

• The values in the Column A cells should be left the same.

formula: =CONCAT(A36,R36).

VBA would probably be preferable since it allows for the insertion of hyperlinks in Column A, where there is already a formula.

UnleashtheData,
Hello, and welcome to the Forum.
You said in the 3rd bullet point 'The values in the Column A cells should be left the same'...but then in the final sentence you state 'VBA would probably be preferable since it allows for the insertion of hyperlinks in Column A, where there is already a formula.' So if column A is to 'be left the same' where are you expecting to place the hyperlink? Column B, same row? Where are you expecting the concatenated value (A36,R36) hyperlink to be placed? Column C same row?

Also, can you provide a sample of what is in columns A and R beginning at row 36?
Maybe with this information we can see how to move forward with a VBA solution.
Perpa

 

[UnleashtheData]

Hello, and thank you so much!

My understanding is that there are two distinct methods for adding hyperlinks:

-- Insert --> Hyperlink, by which a hyperlink can be added to a cell that already contains a formula, and the formula will still work.
-- The HYPERLINK function, which is much more automatic but cannot be used in a cell that already contains another important formula.

I was saying that, ideally, I could use VBA equivalent of Insert --> Hyperlink to get the best of both worlds -- both quickly (I have thousands of these hyperlinks to add over many worksheets and workbooks), and without disrupting the formula that is already in Column A.

So to answer your first question, if we can find a VBA solution for adding the non-formulaic hyperlinks, then we would add them in Column A in the same cells that contain formulas already. If not -- if we go the HYPERLINK function route -- then we would add it to Column B, same rows. (Note that going that route would probably not involve a macro at all, just fixing the Temp file issue. But I'd rather do it the first way.)

Your second question was about where the concatenated (A36,R36) hyperlink should be placed. This macro would place hyperlinks in the same places as the first macro, as it will be used in different worksheets and workbooks than the first macro. So, if using the equivalent of Insert --> Hyperlink, this macro would also place the hyperlinks starting in A36 and continuing downwards to the last nonblank cell.

I made 4 nice dummy workbooks and screenshots depicting different cases, but now I don't think I'll be able to post them. I do not have permission to install the necessary screenshot tools on this work computer. If it's allowed, I'd happily email them to you.

Thanks again, and sorry for any confusion!

 

[Perpa]

UnleashtheData,
Too bad you were unable to post the screenshots, that would have helped tremendously. I don't receive email that don't go through the forum. Perhaps someone else may be willing to assist you with that.

Lacking that, perhaps you can post a few examples of what those values look like, not the actual so it remains confidential, but so we can see the format and what types of data are in column A that we are dealing with.

I am unaware of the method 'by which a hyperlink can be added to a cell that already contains a formula'. I know how to use the 'text' in a cell and make the text a hyperlink in that same cell. So my solution would be to place the hyperlink in column B, leaving the formula in column A alone. But I need to know what we are dealing with in column A, a URL, file folder, or what?

If someone else is aware of the 'formula hyperlink method' you describe I will gladly step aside and learn something.

Let's see what develops from this post.
Perpa

 

[UnleashtheData]

Hello again! Thanks for your response!

Originally, Column A had a very complex formula, but it looks like now we moving to making Column A static values anyway! So please scratch what I said earlier and assume Column A has static values in it. Specifically, it will be a text string with 5 characters (wouldn't bank that it will universally be 5 but that's generally what it is).

In the workbooks where the concatenations are necessary, Column R contains concatenations of a space character followed by an opening parenthesis, three characters pulled from a different cell, another space, 1 OR 2 more characters pulled from a different cell, and a closing parenthesis. In other words, a generic column R value would be " (### ##)" OR "(### #)", with each # representing either a digit or a letter in the text string.

This is in a rollup/summary sheet. There are several such sheets in each workbook, and in between them are breakdown sheets. Each breakdown sheet corresponds one of the rows of data (starting with A36:R36) in a rollup sheet. If a rollup sheet has 10 rows, then the next 10 worksheets will be breakdown sheets corresponding to those rows, and the sheet after that will be the next rollup sheet. The breakdown worksheet names will match the values in column A in workbooks where the concatenations are not necessary. In other words, they will be of the form "#####". The breakdown worksheet names in the workbooks where concatenations are necessary will be of the form "##### (### ##)" -- in other words, a concatenation of the Column A value and the Column R value.
As for the method by which a hyperlink can be added to a cell that already contains a formula, manually clicking Insert à Hyperlink works in cells where there is already a formula. Or do you mean you don’t know a way to do it in VBA?

 

[Perpa]

UnleashtheData,
Having never used a formula cell to create a hyperlink, I did a simple test and now I understand that it is possible to make a hyperlink of the displayed value in a cell and the formula in that cell is preserved.
So if we know the destination location is in the same row, I assumed in column C, then we can create the list of hyperlinks in column A that you wanted in the first place.
Knowing that location we can use 'Address:= Activecell.Offset(0,2)' as in the red font code below.
(If the destination is not in column C same row, then change the Offset to wherever it is).
To select a sheet to create hyperlinks just place an X in cell A1 of each sheet.
Perpa

Sub hyperLINKcreate()
    Dim x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    Dim xG, xOriginal As String
    Dim ws As Worksheet
    Dim wb As Workbook
    
    Set wb = ActiveWorkbook
    For Each ws In wb.Sheets
       If Cells(1, "A") <> "X" Then GoTo mycell     'Put an 'X' without the quotes in cell A1 of all worksheets to have hyperlinks created
       x = 36
       Do Until Cells(x, "A").Value = ""
           Cells(x, "A").Activate
           'xG = ActiveCell.Text                     'Not used
           'xOriginal = ActiveCell.Value     'Not used
     'The following makes the displayed value in column A a hyperlink
              With ActiveSheet
                  .Hyperlinks.Add Anchor:=ActiveCell,[COLOR=#ff0000] Address:=ActiveCell.Offset(0, 2)[/COLOR]
              End With
 
           x = x + 1
       Loop
mycell:
   Next ws
End Sub

 

[UnleashtheData]

Hello again, and thanks for your help! This was a great start, though I made some changes to get it working. Here’s what I’ve got now (note that this one is for the first type of workbook that does not require those concatenations):

Option Explicit
Sub Hyperlink_A_Creator()
    Dim x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    'Dim xG, xOriginal As String
    'Dim ws As Worksheet
    Dim wb As Workbook
    Dim i As Integer
    Dim current As Worksheet
    
    Set wb = ActiveWorkbook
   For i = 1 To wb.Worksheets.Count
    Set current = wb.Worksheets(i)
    'For Each ws In ActiveWorkbook.Worksheets
       'If Cells(1, "S") <> "X" Then GoTo mycell     'Put an 'X' without the quotes in cell A1 of all worksheets to have hyperlinks created
       If current.Tab.ColorIndex = -4142 Then GoTo mycell
       x = 36
       Do Until current.Cells(x, "A").Value = ""
           current.Select
           current.Cells(x, "A").Activate
           'xG = ActiveCell.Text                     'Not used
           'xOriginal = ActiveCell.Value     'Not used
     'The following makes the displayed value in column A a hyperlink
              With current
                  .Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!A1"
              End With
 
           x = x + 1
       Loop
mycell:
   'Next ws
   Next i
End Sub

One of the more important changes was adding the SubAddress field so that the hyperlink will go to the worksheet with the same name as the value of the cell that houses the hyperlink. Another change was the way the macro identifies which worksheets to add the hyperlinks to. I know I hadn’t mentioned this, but each of the rollup sheets (and only the rollup sheets) have a worksheet tab color that has been changed from the default. So “If current.Tab.ColorIndex = -4142” is checking whether that tab color is the default of -4142.

Now my question... Do you know how to create a hyperlink to the last sheet that meets a certain condition? Here are the specifics of what I’m trying to do: I’d like to do is to create some hyperlinks to go back from the breakdown sheets to the rollup sheets. After the user clicks one of the hyperlinks created by the code above to go to one of the breakdown sheets, they would be able to click a hyperlink in cell F1 and return to the previous rollup sheet. There are also always 5 worksheets in each of these workbooks that are neither rollup nor breakdown sheets, that always have the same names, and that need no hyperlinks. I can’t post the names, so we’ll say that these sheets’ names are “Name1”, “Name2”, “Name3”, “Name4”, and “Name5”. My goal is a sub that will:

• Operate on all worksheets except…
  • Any rollup sheets (current.tab.colorindex <> -4142)
  • Sheets with any of the following names: “Name1”, “Name2”, “Name3”, “Name4”, and “Name5”.
• Merge-and-center cells F1:Q1
• Add in that merged cell a hyperlink to the last rollup sheet, the last sheet with current.tab.colorindex <> -4142. Obviously, this could be 1 sheet back or 30, etc.

I think I get can most of this, but it’s the actual add-the-hyperlink code line that I’m not as sure about, since the destination will have a name and position that varies. Any help? Thank you!!

 

[Perpa]

Option Explicit
Sub Hyperlink_A_Creator()
    Dim x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    Dim wb As Workbook
    Dim i As Integer
    Dim current As Worksheet
    
    Set wb = ActiveWorkbook
   For i = 1 To wb.Worksheets.Count
    Set current = wb.Worksheets(i)

       If current.Tab.ColorIndex = -4142 Then GoTo mycell
       x = 36
       Do Until current.Cells(x, "A").Value = ""
           current.Select
           current.Cells(x, "A").Activate

     'The following makes the displayed value in column A a hyperlink
              With current
                  .Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!A1"
              End With
 
           x = x + 1
       Loop
mycell:
   
   Next i
End Sub

UnleashtheData,
I am glad you were able to use the code I furnished and also able to modify it to suit your needs.
The terminology you are using: the 'rollup sheets'> the 'breakdown sheets' and now the 'Named sheets'...It is a bit confusing.
But if I understand correctly, you use the 'rollup sheets' to hold the hyperlinks to each of the 'breakdown sheets', and you do not want to hyperlink to the 'Named sheets'.
The code you created already does not make a hyperlink to sheets with tab colorindex = -4142.
You can omit the 'Named sheets' with a similar IF statement below the IF current.Tab.ColorIndex:

 If current.Name = {“Name1”, “Name2”, “Name3”, “Name4”,  “Name5”} Then GoTo mycell

Then you can place a return hyperlink in cell F1 of the sheets that have been hyperlinked to. Something like the following would go right below the first WITH statement:

With Workbook(ActiveCell.Value).Sheets("Sheet1").cells(1,"F")
     .Hyperlinks.Add Anchor:=current.name, Address:=current
End with

I could not test the above and I wasn't sure what you have in 'ActiveCell.Value' and 'A1' so you will probably have to tweak that to get it to work.
Perpa

 

[UnleashtheData]

Hello again!

Thanks for the help, and sorry for any confusion! I think I'm all good now! Here's what I ended up using for that last part you mentioned:

With wb.Sheets(ActiveCell.Value).Cells(1, "F")
      .Hyperlinks.Add Anchor:=wb.Sheets(ActiveCell.Value).Cells(1, "F"), Address:="", SubAddress:="'" & current.Name & "'!A1", TextToDisplay:="Click to Return to " & current.Name & " Summary Sheet"
End With

Thanks again!

 

[Perpa]

Hello again!

Thanks for the help, and sorry for any confusion! I think I'm all good now! Here's what I ended up using for that last part you mentioned:

With wb.Sheets(ActiveCell.Value).Cells(1, "F")
      .Hyperlinks.Add Anchor:=wb.Sheets(ActiveCell.Value).Cells(1, "F"), Address:="", SubAddress:="'" & current.Name & "'!A1", TextToDisplay:="Click to Return to " & current.Name & " Summary Sheet"
End With

Thanks again!

UnleashtheData,
Glad my code helped you get to your goal. You are welcome, and very good work on your part.
Regards,
Perpa