【ExcelVBA】シートをコピーして挿入

Option Explicit

Public Sub SheetCopy2BookIn2()
    Dim Bk As Workbook
    Dim Sh As Worksheet
    Dim stBookName As String
    Dim stShortName As String
    
    Const cstAPath As String = "C:\temp\A\A.xlsm"
    Const cstSheet As String = "test"
    Const cstBPath As String = "C:\temp\B\"
    
    Set Bk = Workbooks.Open(Filename:=cstAPath, ReadOnly:=True)
    Set Sh = Bk.Worksheets(cstSheet)
    
    stBookName = Dir(cstBPath & "*.xlsm")
    
    Do While stBookName <> ""
        stShortName = Left(stBookName, 1)
        If stShortName = "B" Or stShortName = "C" Or stShortName = "D" Then
            With Workbooks.Open(Filename:=cstBPath & stBookName, ReadOnly:=False)
                Sh.Copy After:=.Worksheets(.Worksheets.Count)
                .Save
                .Close
            End With
        End If
        stBookName = Dir()
    Loop
    
    Bk.Close SaveChanges:=False
    Set Sh = Nothing
    Set Bk = Nothing
End Sub