VBA Hyperlink to Main Sheet

[benntw]

I have a workbook that creates sheets from a list of work order numbers. What I am trying to do is as the code is adding sheets, named as the WO Number, I want it to add a hyperlink in A1 to the main sheet. Here is my code to add sheets from the list of work orders. Hopefully someone can help with this. Thank you.

Sub InsertNamedWorksheets()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("SAP Worklist").Range("E3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Application.ScreenUpdating = False
Application.DisplayAlerts = False


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell

Application.DisplayAlerts = False
Application.ScreenUpdating = False

End Sub

 

[JustynaMK]

Assuming that "SAP Worklist" is your main sheet, try adjusting your For Each loop as follows:

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        With Sheets(MyCell.Value)
            .Hyperlinks.Add _
            Anchor:=.Range("A1"), _
            Address:="", _
            SubAddress:= _
                "'SAP Worklist'!A1", TextToDisplay:="Main Sheet"
        End With
    Next MyCell

 

[gallen]

Untested so test on copy:

Sub InsertNamedWorksheets()
Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("SAP Worklist").Range("E3")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        Sheets(Sheets.Count).Hyperlinks.Add _
        Anchor:=Sheets(Sheets.Count).Range("A1"), SubAddress:="Sheet1!A1", TextToDisplay:="HOME"
    Next MyCell
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

End Sub

 

[benntw]

Assuming that "SAP Worklist" is your main sheet, try adjusting your For Each loop as follows:

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        With Sheets(MyCell.Value)
            .Hyperlinks.Add _
            Anchor:=.Range("A1"), _
            Address:="", _
            SubAddress:= _
                "'SAP Worklist'!A1", TextToDisplay:="Main Sheet"
        End With
    Next MyCell

Thank you very much. It worked perfect !

 

[benntw]

Untested so test on copy:

Sub InsertNamedWorksheets()
Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("SAP Worklist").Range("E3")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
   
    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        Sheets(Sheets.Count).Hyperlinks.Add _
        Anchor:=Sheets(Sheets.Count).Range("A1"), SubAddress:="Sheet1!A1", TextToDisplay:="HOME"
    Next MyCell
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

End Sub

Thank you