1

I would like to understand why PasteSpecial doesn't work with this code.

I'm trying to copy every cell that has the same date of the cell J3 to a new position and then clear the old data. The idea of End(xlDown).Offset(1) is to avoid overwriting but its doens't seem to work.

If I click on debug I get -4121 and I can't understand what does it mean enter image description here

Sub update()

Dim x As Integer

For x = 22 To 36
    If Cells(x, 4).Value = Cells(3, 10).Value Then
        Cells(x, 5).Copy
        Cells(3, 10).End(xlDown).Offset(1).PasteSpecial
        Cells(x, 4).ClearContents
        Cells(x, 5).ClearContents
    End If
    
Next x
   

End Sub

Here an image of my sheet and the idea behind the code enter image description here

Fuboski
  • 113
  • 6
  • 1
    [Use `End(xlUp)` to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Mar 10 '21 at 20:43
  • 2
    FYI if you select J3 (and there's no content below that cell) then pressing `Ctrl+Down` will take you to the bottom of the sheet, so you cannot `.Offset(1, 0)` from there... As @BigBen suggests, use `Cells(Rows.Count, 10).End(xlUp).Offset(1).PasteSpecial` instead. – Tim Williams Mar 10 '21 at 21:04
  • Thank you so much, I will fix it! – Fuboski Mar 10 '21 at 21:29

0 Answers0