VBA - Create Sheets from Range and Apply Hyperlink

[tyroneclark]

So I am having a pretty hard time getting this working...
AIM: Click "UpdateSheets", "ServerTemplate" is copied and the value in each cell in E column is used as the Sheet Name, once done, hyperlink the cell in column E to the respective sheet.
ISSUE: The hyperlink is only linking to the sheet with the name in the last populated cell in column E for all cells in column E; also changing E10 to the same value as the last populated cell in column E
QUESTION: Could someone possibly assist and let me know where I am going wrong?

privatesub updatesheets_click()

Dim wsmaster As Worksheet, wstemp As Worksheet, wsgeneral As Worksheet
Dim wasvisible As Boolean
Dim shnames As Range, nm As Range

With ThisWorkbook

   Set wstemp = .Sheets("servertemplate")
   Set wsmaster = .Sheets("main")
   Set shnames = wsmaster.Range("e10:e" & Rows.Count).SpecialCells(xlConstants)

       wasvisible = (wstemp.Visible = xlSheetVisible)
If Not wasvisible Then wstemp.Visible = xlSheetVisible

   Application.ScreenUpdating = False
   For Each nm In shnames
If Not Evaluate("isref('" & CStr(nm.Text) & "'!a1)") Then
           wstemp.Copy after:=.Sheets(.Sheets.Count)
           ActiveSheet.Name = CStr(nm.Text)
End If
       wsmaster.Hyperlinks.Add anchor:=shnames, Address:="", _
       SubAddress:=CStr(nm.Text) & "!a1", _
   TextToDisplay:=nm.Text
Next nm
wsmaster.Activate
If wasvisible Then wstemp.Visible = xlSheetHidden Else: If Not wasvisible Then wstemp.Visible = xlSheetHidden
   Application.ScreenUpdating = True
For Each wsgeneral In ThisWorkbook.Worksheets
If wsgeneral.Name = "servertemplate(1)" Then
       wsgeneral.Delete
End If
Next wsgeneral
End With
MsgBox "all servers have been added."
ThisWorkbook.Save
End Sub

 

[tyroneclark]

Ok, so I have almost got it but getting an error:

Run-time error '450':

Wrong number of arguments or invalid property assignment


This is alerting on line: wsMASTER.Hyperlinks.Add anchor:=Nm, SubAddress:="'" & Nm & "'!A1"

Heres my code, hopefully someone can assist?

The purpose of this is for each cell in E column (row 10 onwards) should be hyperlinked to the sheet with the same name.

Private Sub UpdateSheets_Click()


Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wsGENERAL As Worksheet
Dim wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range


With ThisWorkbook
    Set wsTEMP = .Sheets("ServerTemplate")
    Set wsMASTER = .Sheets("Main")
    Set shNAMES = wsMASTER.Range("E10:E" & Rows.Count).SpecialCells(xlConstants)
    
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible
    
    Application.ScreenUpdating = False
    For Each Nm In shNAMES
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)
            ActiveSheet.Name = Nm.Text
    Next Nm
        If wasVISIBLE Then wsTEMP.Visible = xlSheetHidden Else: If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden
    For Each Nm In shNAMES
        wsMASTER.Hyperlinks.Add anchor:=Nm, SubAddress:="'" & Nm & "'!A1"
    Next Nm
    For Each wsGENERAL In ThisWorkbook.Worksheets
        If wsGENERAL.Name = "ServerTemplate(1)" Then
            wsGENERAL.Delete
        End If
    Next wsGENERAL
    ActiveSheet.Name = wsMASTER
End With
        Application.ScreenUpdating = True
MsgBox "All servers have been added."
ThisWorkbook.Save
End Sub