シートをコピーして名前を変更するプロシージャ

コピー元のワークシートとコピー後のシート名を指定して、シート複製・名前変更します。

サンプルには記載していませんが、シート操作はエラーが起きやすいので、必要に応じてエラー処理などを入れた方がよいかもしれません。

'*
'* シートをコピーする
'*
'* in  wsBaseSheet   コピー元ワークシート
'*     sNewSheetName コピー後のシート名
'*
Private Sub SheetCopy(ByVal wsBaseSheet As Worksheet, ByVal sNewSheetName As String)

    Dim lFmtIndex As Long
    Dim lNewIndex As Long
    
    'シートをコピーして、コピー元の手前に置く
    wsBaseSheet.Copy Before:=wsBaseSheet
    
    'コピー元シートのインデックスを取得する
    lFmtIndex = Sheets(wsBaseSheet.Name).Index
    'コピー先のインデックスは「コピー元 - 1」
    lNewIndex = lFmtIndex - 1
    
    'コピー先シートの名前を変更する
    ThisWorkbook.Sheets(lNewIndex).Name = sNewSheetName

End Sub

呼び出し例。
「hoge」シートをコピーし、「fuga」シートとします。
同名シートを作成することはできないため、これを二度実行するとエラーとなります。

Public Sub test()
    Call SheetCopy(ThisWorkbook.Sheets("hoge"), "fuga")
End Sub