Excel VBA Copy Template Worksheet and rename based on specific cells/update values of specific cells

JoeCavasin 

Thanks. Here you go:

Dim WBK As Workbook
Dim WSH As Worksheet
Dim RNG As Range
Dim ADR As String
Dim WSN As Worksheet
Dim ID As Long

'Close and save other workbooks
'    For Each WBK In Application.Workbooks
'        If Not (WBK Is Application.ThisWorkbook) Then
'            WBK.Close SaveChanges:=True
'        End If
'    Next WBK
'Find New Users on Productivity Tab
    Set WBK = ThisWorkbook
    Set WSH = WBK.Worksheets("Prod Assembly")
    With WSH.Range("A:A")
        Set RNG = .Find(What:="No", LookIn:=xlValues, LookAt:=xlWhole)
        If Not RNG Is Nothing Then
            ADR = RNG.Address
            Do
                If RNG.Offset(0, 1).Value <> "" Then
                    ID = RNG.Offset(0, 1).Value
                    WBK.Worksheets("Template").Copy After:=WBK.Worksheets(WBK.Worksheets.Count)
                    Set WSN = WBK.Worksheets(WBK.Worksheets.Count)
                    WSN.Range("B2").Value = ID
                    WSN.Name = CStr(ID)
                End If
                Set RNG = .Find(What:="No", LookAt:=xlWhole)
                If RNG Is Nothing Then Exit Do
            Loop Until RNG.Address = ADR
        End If
    End With
    WSH.Activate
End Sub