允许用户选择要从中复制工作表到已打开的工作表中的 Excel 文件

Question

我有一个发票工作簿，我想在其中添加一个宏来提示用户选择一个交易工作簿。然后我想将所选文件的单个工作表复制到发票工作簿中。以下代码在以下行引发了运行时错误 9 下标超出范围。

transactionWorkbook.Sheets("Sheet").Copy After:=Worksheets("Export")

这是我正在使用的代码

' Get transaction workbook...
Dim filter As String
Dim caption As String
Dim transactionFilename As String
Dim transactionWorkbook As Workbook
Dim invoiceWorkbook As Workbook

' make weak assumption that active workbook is the target
Set invoiceWorkbook = Application.ActiveWorkbook

' get the transaction workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
transactionFilename = Application.GetOpenFilename(filter, , caption)

Set transactionWorkbook = Workbooks.Open(transactionFilename)

transactionWorkbook.Sheets("Sheet").Copy After:=Worksheets("Export")




With transactionWorkbook

.Saved = True

.Close

End With
invoiceWorkbook.Sheets("Charge Invoice").Select

End Sub

我不知道交易工作表的范围，或者我只会在用户选择文件后使用 .Range.Copy。我不确定如何实现这一点。非常感谢您的帮助！