I have an invoicing workbook where I want to have a macro that prompts the user to select a transaction workbook. I want to then copy the selected file’s single worksheet into the invoicing workbook. The following throws a runtime error 9 subscript out of range at the following line.
transactionWorkbook.Sheets("Sheet").Copy After:=Worksheets("Export")
Heres the code I’m working with
' 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
I don’t know the range of the transaction worksheet or I’d just use .Range.Copy after the user had selected the file.
I’m not sure how to get this to happen. Many thanks for any help!
