vba复制整个sheet内容_VBA一键复制当前文件夹全部EXCEL里的工作表

【关键步骤】从开发工具里打开Visual Basic, 新建模块1，将以下代码复制到里面，保存，关闭代码窗口。

Public Sub 一键获取本文件夹工作表()

Application.ScreenUpdating = False

Dim f As String, i As Integer

Dim wb As Excel.Workbook

Dim sh, sh1 As Excel.Worksheet

Set sh1 = ThisWorkbook.Worksheets("导入清单")

If Range("a65536").End(xlUp).Row > 1 Then

sh1.Range("a2:b" & Range("a65536").End(xlUp).Row).Clear

End If

f = Dir(ThisWorkbook.Path & "\*xls*")

Do While f <> ""

If f <> ThisWorkbook.Name Then

Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f)

For i = 1 To Sheets.Count

sh1.Range("a" & sh1.Range("a65536").End(xlUp).Row + 1) = wb.Name

sh1.Range("b" & sh1.Range("b65536").End(xlUp).Row + 1) = Sheets(i).Name

Next

Worksheets.Copy Before:=Workbooks(ThisWorkbook.Name).Sheets(1)

wb.Close True

End If

f = Dir

Loop

sh1.Select

Application.ScreenUpdating = True

MsgBox "已为您成功导入" & Sheets.Count - 1 & "张工作表", , "VBA交流QQ15678768"

End Sub