Type Mismatch in PasteSpecial xlPasteValues

Question

I want to copy the contents of two excel in one, and all goes well until I copy the contents of my second excel, because this overwrites what the first copy excel, done that :

These are my statements:

Dim wbOrigen1 As Workbook, _
    wbOrigen2 As Workbook, _
    wsDestino As Excel.Worksheet, _
    wsOrigen1 As Excel.Worksheet, _
    wsOrigen2 As Excel.Worksheet, _
    rngOrigen1 As Excel.Range, _
    rngDestino As Excel.Range, _
    rngDestino2 As Excel.Range, _
    rngOrigen2 As Excel.Range

Here is the problem

ThisWorkbook.Activate
Set rngDestino2 = wsDestino.Range(celdaDestino,Range(celdaDestino).End(xlDown).Offset(1, 0))
Range(celdaDestino).End(xlDown).Offset(1, 0).Select
wsOrigen2.Activate
rngOrigen2.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ThisWorkbook.Activate

rngDestino2.PasteSpecial xlPasteValues
Application.CutCopyMode = False

But this does not work the same way. The problem seems to be with rngDestino2

?rngDestino2
Type Mismatch
?err.Description
Type Mismatch

How I can fix it?

a) Where ae you setting `wsDestino`? Is `celdaDestino` set to a cell (or range of cells) on the `wsDestino` worksheet? Your description of the problem is sufficient but the sample code is insufficient to see where the problem is. Please see [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — , Mar 17 '15 at 16:01
Ok sorry, is my first day... xD wsDestino setting is "Set wsDestino = Worksheets("procesos consolidado")" celdaDestino setting is = " Const celdaDestino = "A1" " the problem is with this line: Set rngDestino2 = wsDestino.Range(celdaDestino,Range(celdaDestino).End(xlDown).Offset(1, 0)) I need to find the last cell used , and copy the contents of the book(wsDestino) here,. — YotiS, Mar 17 '15 at 16:11

score 1 · Accepted Answer · edited May 23 '17 at 12:12

I prefer to avoid select, activate and selection in deference to direct addressing. The paste special, values can also be handled more efficiently by direct cell value transfer.

'make sure that the worksheet vars are set correctly
set wsOrigen2 = <other workbook>.Sheets("Sheet1")
set wsDestino = ThisWorkbook.Sheets("Sheet1")

With wsOrigen2.Cells(1, 1).CurrentRegion
    wsDestino.Cells(Rows.Count, 1).End(xlUp) _
      .Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Cells.Value
End With

set wsDestino = nothing
set wsOrigen2 = nothing

If the workbooks are open and the worksheets are set correctly, that should be all that you really require.

See How to avoid using Select in Excel VBA macros for more methods on getting away from replying on select and activate.

Type Mismatch in PasteSpecial xlPasteValues

1 Answers1