【Excel】VBAマクロでシートコピーを別ブックへ（から）し（複数も）保存や（末尾、最後）名前変更する方法【Copy, SaveAs】

Sub SheetKopiToBetsuBukku()
    Dim MotoSheet As Worksheet
    Dim KopiBukku As Workbook
    Dim HozonName As String

    ' 元となるシートを選択
    Set MotoSheet = ThisWorkbook.Sheets("Sheet1")

    ' 新しいブックを作成してコピー
    MotoSheet.Copy
    Set KopiBukku = ActiveWorkbook

    ' 保存名の指定
    HozonName = KopiBukku.Path & "\Kopi.xlsx"
    KopiBukku.SaveAs Filename:=HozonName
    KopiBukku.Close
End Sub

Sub TakusanSheetKopi()
    Dim MotoSheets() As String
    Dim KopiBukku As Workbook
    Dim HozonName As String

    ' コピーしたいシート名を配列に格納
    MotoSheets = Array("Sheet1", "Sheet2", "Sheet3")

    ' シートを新しいブックにコピー
    ThisWorkbook.Sheets(MotoSheets).Copy
    Set KopiBukku = ActiveWorkbook

    ' 保存名の指定
    HozonName = KopiBukku.Path & "\TakusanKopi.xlsx"
    KopiBukku.SaveAs Filename:=HozonName
    KopiBukku.Close
End Sub

Sub TakusanBukkuKopi()
    Dim MatomeruBukku As Workbook
    Dim BetsuBukku As Workbook
    Dim BetsuBukkuPath As String
    Dim HozonName As String

    ' まとめるブックを新規作成
    Set MatomeruBukku = Workbooks.Add

    ' 別ブックからシートをコピー
    BetsuBukkuPath = "C:\path\to\your\file.xlsx"
    Set BetsuBukku = Workbooks.Open(BetsuBukkuPath)
    BetsuBukku.Sheets("Sheet1").Copy After:=MatomeruBukku.Sheets(MatomeruBukku.Sheets.Count)
    BetsuBukku.Close

    ' 保存名の指定
    HozonName = MatomeruBukku.Path & "\MatomeruKopi.xlsx"
    MatomeruBukku.SaveAs Filename:=HozonName
    MatomeruBukku.Close
End Sub