Macro for Creating a New Sheet and Navigation Links

[IanKruman]

Hello All,

I am currently creating an excel file that is used for outlining steps for a job procedure in an index sheet and then providing detailed instructions for each step in a separate sheet. On the index sheet next to each step is a link to the sheet containing the details for that step. Similarly there are navigation links in the step details sheets that point to next and previous pages.

I am trying to create a macro that adds an additional line for a new step to the index sheet, creates and names a new sheet for the new step, and then directs the links to the appropriate sheets.

The problem I am having is the part where I try to assign the destination of the links. I can't figure out how to make sure the links go to the right destination when using the macro multiple times. For example this what I have tried to use for directing a link to the new page created when running the macro:


LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR).Select
Selection.Hyperlinks(1).SubAddress = " '=Sheets(Sheets.Count)'!A1"


Where LR is the last row, "A" & LR is the location of the link I'm trying to change, and Sheets(Sheets.Count) is the last sheet in the file (and the destination of the link I'm trying to get).

The last line of code doesn't work of course but hopefully it illustrates what I'm trying to do. If the name of the last sheet is "11" code that would work would be


Selection.Hyperlinks(1).SubAddress = " '11' ! A1"


but I need the macro to work multiple times so I can't use a static reference like this.

Any help would be greatly appreciated. You have my apologies for being clueless in this department, I had never tried writing a macro in excel until yesterday.

Thanks in Advance,
Ian K

 

[Jerry Sullivan]

Welcome to the Board, Ian!

You can make a reference to the last sheet that will work as a Hyperlink SubAddress using this syntax:

"'" & Sheets(Sheets.Count).Name & "'!A1"

I am trying to create a macro that adds an additional line for a new step to the index sheet,
creates and names a new sheet for the new step, and then directs the links to the appropriate sheets.

Instead of creating a Hyperlink, then adding the sheet, then changing the Hyperlink subaddress...
you might consider adding the sheet first, then creating a Hyperlink to that new sheet in one step.

The Hyperlink.Add method works like this...

Sub Add_Hyperlink_to_LastSheet()
    With ActiveSheet
        .Hyperlinks.Add _
            Anchor:=.Range("A" & Rows.Count).End(xlUp).Offset(1), _
            Address:="", _
            SubAddress:= "'" & Sheets(Sheets.Count).Name & "'!A1", _
            ScreenTip:="Click me", _
            TextToDisplay:=Sheets(Sheets.Count).Name
    End With
End Sub