VBA: 将多个工作簿的第一张工作表合并到一个工作簿中

Option Explicit

Sub MergeWorkbook()

    '将多个工作簿的第一张工作表合并到目标工作簿中
    
    Dim folder_path As String, filename As String
    
    Dim first_sheet As Worksheet, wb As Workbook, ii As Integer
    
    Dim temp_name As String

    '关闭屏幕刷新
    Application.ScreenUpdating = False
    
    '当前文件夹的路径
    folder_path = ThisWorkbook.path
    
    filename = Dir(folder_path & "\*.xls*")
    
    While filename <> ""
    
        If filename <> ThisWorkbook.Name Then
        
            Set wb = Workbooks.Open(folder_path & "\" & filename)
            
            temp_name = Left(wb.Name, InStr(wb.Name, ".") - 1)  '工作簿名称，不包含文件拓展名
            
            Set first_sheet = ActiveWorkbook.Worksheets(1)
            
            ii = ThisWorkbook.Sheets.Count
            first_sheet.Copy After:=ThisWorkbook.Sheets(ii)
            
            ActiveSheet.Name = temp_name
                
            wb.Close
        
        End If
        
        filename = Dir
        
    Wend
    
    '恢复屏幕刷新
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
    
End Sub