如何修复vba中用于在工作簿之间复制工作表的下标超出范围错误

Question

有人能帮我在vba中完成以下代码吗。运行时，它显示错误9-下标超出范围。

Sub add_sheet()
cwb = ThisWorkbook.FullName
booktoopen = "C:UsersmfDesktopBOI Bills VerReportsSrikakulam.xlsx"
Workbooks.Open(booktoopen).Activate
Workbooks(cwb).Sheets("Sheet2").Copy Before:=Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Workbooks(booktoopen).Close savechanges = True
End Sub

Answer 1

直接使用工作簿变量，而不是使用它们的名称，并正确声明所有变量(使用Option Explicit！(。

同样命名的参数需要:=，而不仅仅是像SaveChanges:=True那样的=

Option Explicit
Public Sub add_sheet()
Dim PathToOpen As String  ' define path of the workbook
PathToOpen = "C:UsersmfDesktopBOI Bills VerReportsSrikakulam.xlsx"
Dim OpenedBook As Workbook  ' open workbook and reference it to a variable that we can use later
Set OpenedBook = Workbooks.Open(PathToOpen)
ThisWorkbook.Sheets("Sheet2").Copy Before:=OpenedBook.Sheets("Sheet1")
OpenedBook.Close SaveChanges:=True
End Sub