シートのコピーまたは追加と同時にシート名を設定する。

VBAでシートをCopyメソッドでコピーした直後は「コピー元のシート名＋（連番）」、Addメソッドで追加した場合は「Sheet＋（連番）」が初期値としてシート名に設定されます。

このコピーや追加と同時にシート名を設定するには、Copyメソッドの場合は処理したあとはコピーしたシートがActieveな状態になっているので、ActiveSheet.Name に設定したいシート名を代入することで設定できます。
Addメソッドの場合は処理直後のActieveな状態で変更する方法とシートの追加と同時にオブジェクト情報をオブジェクト変数に代入した後に設定する方法があります。

　●コピーと同時にシート名を設定

'******************************************************************
'　シートのコピーと同時にシート名を変更
'******************************************************************
Sub setShtNameAfterCopy()

    '[Sheet1]を右端にコピーする
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
    
    'ActiveSheet（コピーされたシート）の名称を設定します。
    ActiveSheet.Name = "COPY-" & Format(Time(), "hhmmss")

End Sub

　■実行前

　■実行結果

　●追加と同時にシート名を設定

'******************************************************************
'　シートの追加と同時にシート名を設定
'******************************************************************
Sub setShtNameAfterAdd()
    
    'Activeなシートの名前を設定
    '-----------------------------------------------------------------------
    '右端にシートを追加する
    Worksheets.Add After:=Sheets(Sheets.Count)
    
    'ActiveSheet（追加されたシート）の名称を設定します。
    ActiveSheet.Name = "Add01-" & Format(Time(), "hhmmss")
    
    
    
    'オブジェクト変数を利用して設定
    '-----------------------------------------------------------------------
      Dim wsAddSheet As Worksheet
      '右端にシートを追加 ＋ シートのオブジェクト情報をオブジェクト変数に設定
      Set wsAddSheet = Worksheets.Add(After:=Sheets(Sheets.Count))
      
      '変数に設定されたオブジェクト（ワークシート）の名称を設定
      wsAddSheet.Name = "Add02-" & Format(Time(), "hhmmss")


End Sub

　■実行前

　■実行結果