VBA Code to Copy and Rename Sheets

CopySheets

'http://www.mrexcel.com/forum/excel-questions/150715-copy-place-rename-worksheet-using-visual-basic-applications.html
Public Sub SheetCopy()
    Dim Sh As Worksheet, TemplateSh As Worksheet
    Dim ShNum As Integer, HighestNum As Integer
    Dim SheetCoreName As String
    
    ' INDICATE THE CORE SHEET NAME
    SheetCoreName = "Sheet"
    
    ' INDICATE THE SOURCE SHEET
    Set TemplateSh = Sheets("Template")
    
    
    ' DETERMINE NEXT NUMBER FOR SHEET
    For Each Sh In Worksheets
    If InStr(1, Sh.Name, SheetCoreName) = 1 Then
        ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))
        If ShNum > HighestNum Then HighestNum = ShNum
    End If
    Next Sh
    
    ' COPY TEMPLATE
    TemplateSh.Copy after:=Sheets(Sheets.Count)
    
    ' MAKE VISIBLE
    ActiveSheet.Visible = xlSheetVisible
    
    ' RENAME
    ActiveSheet.Name = SheetCoreName & HighestNum + 1
    
End Sub