vba - Excel VBA代码: Having troubles opening new workbook,将范围复制到原始工作簿

Question

早上好-

我已经设法拼凑出一个代码，该代码将从我的原始工作簿中打开一个新工作簿，复制特定范围并将该范围粘贴回原始工作簿，然后关闭引用的工作簿。我已经测试了代码的每个部分，在我一起运行整个代码之前，它似乎工作得很好。

现在我不断地得到一个

'subscript out of range'

错误，无论我尝试修改什么，都不会消失。我已尽我所能进行研究，试图找出其中的差异。

一如既往，感谢 StackOverflow 用户帮助我解决问题!

Sub import()
  Dim x As Workbook
  Dim y As Workbook

'## Open both workbooks first:
  Set y = ThisWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx"
        .AllowMultiSelect = False
        .Show
    End With
  Set x = ActiveWorkbook

'##Now, copy what you want from x:
  x.Sheets("QBEPaymentAdvice").Range("D13", "T60").Copy


'##Now, paste to y worksheet:
  y.Sheets("QBE").Range("A1").PasteSpecial

'##Close Workbook x
  x.Close

End Sub

Answer 1

试试这个方法:

Sub import()
  Dim x As Workbook
  Dim y As Workbook

'## Open both workbooks first:
  Set y = ThisWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx"
        .AllowMultiSelect = False

                If .Show Then
           xlFileName = .SelectedItems(1)
        Else
           'if user pressed CANCEL - exit sub
           MsgBox "User pressed CANCEL"
           Exit Sub
        End If


    End With
  Set x = Workbooks.Open(xlFileName)

'##Now, copy what you want from x:
  x.Sheets("QBEPaymentAdvice").Range("D13", "T60").Copy


'##Now, paste to y worksheet:
  y.Sheets("QBE").Range("A1").PasteSpecial

'##Close Workbook x
  x.Close

End Sub