1

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?

Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56
Katie
  • 198
  • 3
  • 16
  • This error could be happening if the workbook hasn't been saved. Try saving and then pasting the values. http://stackoverflow.com/questions/17281872/error-pastespecial-method-of-range-class-failed – Matts Feb 11 '17 at 04:45

1 Answers1

3

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
YowE3K
  • 23,852
  • 7
  • 26
  • 40