excel - 类型不匹配 - 将范围从一个工作簿复制到另一个工作簿

Question

我正在尝试使用下面的代码将一个范围从一个工作簿复制到另一个工作簿。此处和其他地方与此问题类似的其他帖子似乎仅限于特定的语法错误，这些错误与我的具体情况(我的代码的最后一行)无关(据我所知)。对于通常尝试在工作簿之间复制和粘贴给定范围(硬编码)的任何人来说，这可能是相关的:

Sub ImportT12Accounts()
'
' ImportT12Accounts Macro
' Pulls in the list of account numbers from a report of the user's choice. 
'
'

Dim fileChoice As Integer
Dim filePath As String
Dim sheetName As Variant
Dim ws0 As Worksheet 'this workbook's 2nd tab
Dim ws1 As Worksheet 'the opened workbook's 2nd tab
Dim wb0 As Workbook 'this workbook (the log)
Dim wb1 As Workbook 'the opened T12 sheet
Dim rng0 As Range 'the range of cells in this workbook's 2nd sheet to be copied to
Dim rng1 As Range 'the range of cells from the openeed workbook to be copied from


Set ws0 = ActiveSheet
Set wb0 = ActiveWorkbook
Set rng0 = Range("B9:B159")

'Find the desired T12 workbook filepath
  'only allow the user to select one file
  Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
  'make the file dialog visible to the user
  fileChoice = Application.FileDialog(msoFileDialogOpen).Show
  'determine what choice the user made
  If fileChoice <> 0 Then
      'get the file path selected by the user
      filePath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
  End If

'Set variables using the newly-opened workbook
Set wb1 = Workbooks.Open(filePath)
Set ws1 = ActiveSheet
Set rng1 = Range("A9:A159")

'Use the filepath selected by User in formulas to pull the account numbers into this book, in Sheet 2
Workbooks(wb0).Worksheets(ws0).Range(rng1).Value = _
Workbooks(wb1).Worksheets(ws1).Range(rng0).Value

End Sub

运行时，它会抛出 "Run-time error '13': Type mismatch"最后一行出现错误，"Workbooks(wb0)...Range(rng0).Value" .

我已经尝试将这种复制粘贴方法替换为其他一些方法，但没有成功。例如，我尝试替换范围变量 .Range(rng0)和.Range(rng1)与/针对.Range("A9:A159")和.Range("B9:B159")直接，但得到同样的错误。

我尝试过的另一个方法示例是:

Workbooks(wb1).Worksheets(ws1).Range(rng1).Copy 
Destination:=Workbooks(wb0).Worksheets(ws0).Range(rng0)

但这给了我同样的错误。

我感觉不匹配是由工作簿或工作表变量之一引起的，但是，我不明白为什么会出现这种情况。据我所知，将工作簿、工作表和范围变量传递到各自的方法中是可以的。

Answer 1

这似乎是对对象的误解。发生错误的原因是您将对象传递到字符串字段，这导致“类型不匹配”。这些对象可以直接调用，并且它们是完全符合声明的。您不需要像那样堆叠它们。

Sub ImportT12Accounts()
    '
    ' ImportT12Accounts Macro
    ' Pulls in the list of account numbers from a report of the user's choice.
    '
    '

    Dim fileChoice As Integer
    Dim filePath As String
    Dim sheetName As Variant
    Dim ws0 As Worksheet                         'this workbook's 2nd tab
    Dim ws1 As Worksheet                         'the opened workbook's 2nd tab
    'Dim wb0 As Workbook                          'this workbook (the log)
    Dim wb1 As Workbook                          'the opened T12 sheet
    Dim rng0 As Range                            'the range of cells in this workbook's 2nd sheet to be copied to
    Dim rng1 As Range                            'the range of cells from the openeed workbook to be copied from


    'Set wb0 = ActiveWorkbook
    Set ws0 = ActiveSheet
    Set rng0 = ws0.Range("B9:B159")

    'Find the desired T12 workbook filepath
    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'make the file dialog visible to the user
    fileChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If fileChoice <> 0 Then
        'get the file path selected by the user
        filePath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    End If

    'Set variables using the newly-opened workbook
    Set wb1 = Workbooks.Open(filePath)
    Set ws1 = ActiveSheet
    Set rng1 = ws1.Range("A9:A159")

    'Use the filepath selected by User in formulas to pull the account numbers into this book, in Sheet 2
    rng1.Value = rng0.Value

End Sub