看看这个

Sub CopySheets()
    
    With Application
    
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    
    End With
    
    'Path for Excel3 file
    Dim strFilename3 As String: strFilename3 = "E:\SO1\Excel3.xlsx"
    
    Set wb3 = Workbooks.Open(Filename:=strFilename3)
    
    'Path for Excel1 file
    Dim strFilename1 As String: strFilename1 = "E:\SO1\Excel1.xlsx"
    
    Set wb1 = Workbooks.Open(Filename:=strFilename1)
    
    'Each worksheet in a workbook
    For Each ws1 In wb1.Worksheets
        
        If ws1.Name = "report1" Then
            
            'copy sheet name to a variable
            'str1 = ws1.Name
            ws1.Copy Before:=Workbooks("Excel3.xlsx").Sheets(1)
            
        End If
    
    Next ws1
    
    wb1.Close SaveChanges:=False
    
    'Path for Excel2 file
    Dim strFilename2 As String: strFilename2 = "E:\SO1\Excel2.xlsx"
    
    Set wb2 = Workbooks.Open(Filename:=strFilename2)
    
    'Each worksheet in a workbook
    For Each ws2 In wb2.Worksheets
        
        If ws2.Name = "report2" Then
            
            'copy sheet name to a variable
            'str2 = ws2.Name
            ws2.Copy Before:=Workbooks("Excel3.xlsx").Sheets(1)
            
            
        End If
    
    Next ws2
    
    wb2.Close SaveChanges:=False
    
    wb3.Close SaveChanges:=True
    
    
    With Application
    
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    
    End With

    MsgBox ("Completed")
    
End Sub