java 多个 excel 合并_excel vba将多个工作簿中的多个工作表合并到一个工作簿中

我有两个excel工作簿，我需要从一个和一组工作表中取出一组工作表，然后将其另存为新工作簿 . 由于我将每周做这个，我想将它保存为宏/ vba .

我在网上发现了这个代码并对其进行了编辑，但它无效 .

Sub CopySheets()

Dim wkb As Workbook

Dim sWksName As String

sWksName = "Store 1"

For Each wkb In Workbooks

If wkb.Name <> ThisWorkbook.Name Then

wkb.Worksheets(sWksName).Copy _

Before:=ThisWorkbook.Sheets(1)

End If

Next

Set wkb = Nothing

sWksName = "Store 3"

For Each wkb In Workbooks

If wkb.Name <> ThisWorkbook.Name Then

wkb.Worksheets(sWksName).Copy _

Before:=ThisWorkbook.Sheets(1)

End If

Next

Set wkb = Nothing

sWksName = "Store 30"

For Each wkb In Workbooks

If wkb.Name <> ThisWorkbook.Name Then

wkb.Worksheets(sWksName).Copy _

Before:=ThisWorkbook.Sheets(1)

End If

Next

Set wkb = Nothing

sWksName = "Store 33"

For Each wkb In Workbooks

If wkb.Name <> ThisWorkbook.Name Then

wkb.Worksheets(sWksName).Copy _

Before:=ThisWorkbook.Sheets(1)

End If

Next

Set wkb = Nothing

End Sub

我必须打开两个工作簿，这没问题 . 工作表“存储1”被复制正常然后停止，当我点击调试时，它告诉我这行有错误

wkb.Worksheets(sWksName).Copy _

Before:=ThisWorkbook.Sheets(1)

错误消息：“脚本超出范围”