Can't Cut and Paste data from one sheet to another

Question

I'm trying to cut the data from all the rows until the last one from sheet "Summary" and paste it to the next blank row in the "Historical" sheet, but im getting this error: PasteSpecial method or Range class failed.

Sub HistoricalData()

  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Dim LastRow As Integer

  LastRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).row

  Set copySheet = Worksheets("Summary")
  Set pasteSheet = Worksheets("Historical")

  copySheet.Range("A2:V2" & LastRow).Cut
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True

End Sub

Thanks in advance !

I think you are using the function PasteSpecial in a wrong way. If you cut the data you cannot use pastespecial. You should first copy the data and PasteSpecial with Values and after that you can delete the same range. — Mikku

Mikku Mikku · Accepted Answer · 2019-06-07T04:34:14

This should do the Job For you:

First we are copying the Range and Pasting Values, on the next step we are clearing that range in where we copied it from.

Sub HistoricalData()

  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Dim LastRow As Integer

  LastRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

  Set copySheet = Worksheets("Summary")
  Set pasteSheet = Worksheets("Historical")

  copySheet.Range("A2:V" & LastRow).Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  copySheet.Range("A2:V" & LastRow).Clear

  Application.CutCopyMode = False
  Application.ScreenUpdating = True

End Sub

Can't Cut and Paste data from one sheet to another

1 Answers