Import Sheets 汇入其他文件的工作表

Sub Import_Sheets()
    ' 第1步：声明变量，第2步：关闭屏幕更新和警告提示
    Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 第3步：初始化目录，第4步：建立循环
    directory = "c:\test\"
    fileName = Dir(directory & "\*.xl??")

    Do While fileName <> ""

        ' 第5步：打开文件
        Workbooks.Open (directory & fileName)

        ' 第6-8步：把这个文件的每张工作表都复制到当前工作簿末尾
        For Each sheet In Workbooks(fileName).Worksheets
            total = Workbooks("import-sheets.xlsm").Worksheets.count
            Workbooks(fileName).Worksheets(sheet.Name).Copy _
            after:=Workbooks("import-sheets.xlsm").Worksheets(total)
        Next sheet

        ' 关闭这个文件，继续下一个
        Workbooks(fileName).Close
        fileName = Dir()

    Loop

    ' 第9步：恢复屏幕更新和警告提示
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub