VBA Paste function throwing error after VBA Clear function

Question

I have an advanced filter that is being used to sort a large data set.

The filter is dropping the filtered data into a separate sheet.

I have a VBA Macro that allows me to highlight the portions of the filter that I want to use and paste it into a range adjacent to the filter table.

Currently I am using very simple VBA. The copy of the active selection and paste into the next open row after a specified Cell. The cell is a row of headers that corresponds to the headers of the table that the copy selection is being made from.

 Sub CopyPaste()
      Selection.Copy
      ActiveSheet.Range("J6").End(xlDown).Offset(1, 0).Select
      ActiveSheet.Paste
 End Sub

The clear is very simple.

Sub ClearTable()

    ActiveSheet.Range("J7:O100").Clear

End Sub

After the clear is run, I receive an error.

Get Run Time error '1004, Application-defined or object defined error.

EDIT: Clarification. .clear and .clearcontents both lead to the errored state If I attempt to paste after I clear the range.

What do you expect .clear to do? I think you mean .clearcontents? — Wolfie
The problem isn't with the clear. Clear works fine. After the clear, I am getting an error with the paste. Edit: Tried Clearcontents. Error Still shows when I attempt to paste again. — Rj Joplin
With J7:O100 cleared, select J6 and tap [ctrl]+[down arrow]. Try and go one more row further down. That is where you are trying to paste and if you follow my directions the problem should be perfectly obvious. — user4039065
Use Selection.Copy Destination:=ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Offset(1, 0) ; this looks from the bottom up and selects 1 row down not from the top down (which seems to be J1048576 and cannot be moved down a row let alone having room for the paste). — user4039065
Thank you very much. I can see what Jeeped was saying now, and thank for for the Destination:= code. I hadn't run across this yet. — Rj Joplin

Unknown Unknown · Accepted Answer · 2017-05-14T21:33:56

With J7:O100 cleared, select J6 and tap [ctrl]+[down arrow]. Try and go one more row further down. That is where you are trying to paste and if you follow my directions the problem should be perfectly obvious.

Use,

Selection.Copy Destination:=ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)

This looks from the bottom up and selects 1 row down not from the top down (which seems to be J1048576 and cannot be moved down a row let alone having room for the paste).

VBA Paste function throwing error after VBA Clear function

1 Answers