Type Mismatch - Copy range from one workbook to another

Question

I am trying to copy a range from one workbook to another, using the code below. The other posts similar to this issue on here and elsewhere seem to be confined to specific syntax errors which aren't relevant (as far as I'm aware) to my specific case (last line of my code). For anyone generally trying to copy and paste a given range (hard-coded) between workbooks, this may be relevant:

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

When run, it throws the "Run-time error '13': Type mismatch" error on the last line, "Workbooks(wb0)...Range(rng0).Value".

I have tried subbing out this copy-paste method for a few others, without avail. For example, I have tried subbing out the range variables .Range(rng0) and .Range(rng1) with/for .Range("A9:A159") and .Range("B9:B159") directly, but get the same error.

Another example of a method I tried is:

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

But this gave me the same error.

I have a feeling the mismatch is being caused by one of the workbook or worksheet variables, however, I can't figure out why this would be the case. From what I can tell, it is fine to pass workbook, worksheet, and range variables into their respective methods.

Answer 1

This seems to be a misunderstanding of objects. The error occurs because you are passing the objects in to a string field which results in "type mismatch". The objects can be called directly and they are fully qualified as declared. You don't need to stack them like that.

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