VBA Visible Cell Paste Error 1004

Question

I have a VBA code that is getting a 1004 error when pasting visible cells:

PasteSpecial method of Range class failed

I can comment out the paste and it will copy the visible cells so I can manually paste them, but the automated paste causes the error. I have confirmed that the sheet names are correct and have tried various types of paste special and just .Paste.

This is my code:

'Copies Column A visible rows
Sheets("Sheet1").Range("$A$2:$A$" & lastRow).SpecialCells  (xlCellTypeVisible).Copy

'Empties sheet
Sheets("Sheet2").Cells.ClearContents

'Pastes to sheet2
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

Can anyone suggest a solution?

This error could be happening if the workbook hasn't been saved. Try saving and then pasting the values. stackoverflow.com/questions/17281872/… — Matts

YowE3K YowE3K · Accepted Answer · 2017-02-11T04:17:19

Move the ClearContents prior to the Copy - the ClearContents method is an operation that empties the clipboard.

'Empties sheet
Sheets("Sheet2").Cells.ClearContents

'Copies Column A visible rows
Sheets("Sheet1").Range("$A$2:$A$" & lastRow).SpecialCells(xlCellTypeVisible).Copy

'Pastes to sheet2
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

VBA Visible Cell Paste Error 1004

1 Answers