Error 1004 - This action won't work on multiple selections

[sharky12345]

I'm having an issue trying to copy and paste data from another workbook - this has worked fine for years but due to a recent change in our software which produces the workbooks I'm encountering an issue.

Here's the code I'm using;

Set WB = Workbooks.Open(CPFile)

WB.Sheets(1).Activate
LastDataRow = WB.Sheets(1).Range("A65536").End(xlUp).Row
WB.Sheets(1).Range("A2:E" & LastDataRow).Copy

ThisWorkbook.Worksheets("IMPORT").Activate
ThisWorkbook.Worksheets("IMPORT").Range("A2").PasteSpecial (xlPasteValues)

Up until recently, the source workbook was saved as an Excel 2003 file with the extension xls, but we changed our system recently and now the source files are saved as Excel 2016 files with the extension xlsx. Now I'm getting an error which fails at this line;

ThisWorkbook.Worksheets("IMPORT").Range("A2").PasteSpecial (xlPasteValues)

The error is 1004 - This action won't work on multiple selections.

If I save the source file as xls then it works fine, but generates the error if the file is saved as a xlsx file, the data is exactly the same - there are only 1165 rows of data if that is relevant.

Can anyone help me understand what is going on please?

 

[NdNoviceHlp]

Hi sharky12345. You can trial skipping the copy/paste and use resize instead. HTH. Dave

Dim Rng As Range
Set WB = Workbooks.Open(CPFile)
WB.Sheets(1).Activate
LastDataRow = WB.Sheets(1).Range("A65536").End(xlUp).Row
Set Rng = WB.Sheets(1).Range("A2:E" & LastDataRow)
ThisWorkbook.Worksheets("IMPORT").Activate
With ThisWorkbook.Worksheets("IMPORT")
.Cells(2, 1).Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With