Hi ALl,

Had to make a new account but I am glad to be back on the forum \\

I've been working on a macro that makes copies of a template sheet based on a table in my Opps sheet. If column B isn't empty, make a copy of the template sheet, rename it to Opps column A, and then hyperlink column A's current A.row to the newly copied and renamed sheet.

I'm not sure what is wrong exactly, it keeps making duplicate Template(x) and stops renaming them, and the hyperlinks are not working. -This is my first go at VBA hyperlinks to internal workbook sheets so any help there would of course be greatly appreciated as well

Happy Funday Monday!
Sub Template()
    Dim ws As Worksheet, wsTemp As Worksheet
    Dim MyCell As Range, MyRange As Range
    Dim LRow As Long
    Dim newsh As Worksheet
     
    Set ws = ThisWorkbook.Sheets("Opps")

    With ws
        LRow = .Range("A" & .Rows.Count).End(xlUp).row

        Set MyRange = .Range("A1:A" & LRow)

        For Each MyCell In MyRange
            If Len(Trim(MyCell.Value)) <> 0 Then
                On Error Resume Next
                Set wsTemp = ThisWorkbook.Sheets(MyCell.Value)
                On Error GoTo 0

                If wsTemp Is Nothing Then
                    ThisWorkbook.Sheets("Template").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
                    ThisWorkbook.Sheets(Sheets.Count).Name = MyCell & "_Harrison" `<-- this is where it usually gets stuck :(
   newsh = ActiveSheet
    ws.Activate
  ws.Hyperlinks.Add MyCell, "", newsh.Name & "!A3", _
"", "HragGGello"

        newsh.Visible = xlSheetHidden `<-- my overall goal is to make it so the copied and renamed sheets can only be accessed via the hyperlinks in the Opps sheet. 
`after I figure this hyperlink issue out I want to create a hyperlink from the newly copied sheet back tot he correct Opps column A cell
                         
                End If

                Set wsTemp = Nothing

            End If
        Next MyCell
    End With
End Sub