VBA粘贴函数在VBA清除后抛出错误

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.

我有一个VBA宏，它允许我突出显示要使用的过滤器的部分，并将其粘贴到与过滤器表相邻的范围中。

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.

目前我正在使用非常简单的VBA。活动选择的副本，并粘贴到指定单元格之后的下一行。单元格是一列标题，对应于复制选择所在的表的标题。

 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.

获取运行时错误'1004、应用程序定义的错误或对象定义的错误。

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

编辑:澄清。.clear和.clearcontents会导致在清除范围后粘贴的错误状态。

1 个解决方案

#1

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.

使用J7:O100清除，选择J6和tap [ctrl]+[向下箭头]。再往下走一排。这就是你试图粘贴的地方，如果你按照我的指示，问题应该是非常明显的。

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).

它从底部向上看，然后从上向下选择1行而不是从上向下(看起来是J1048576，不能向下移动一行，更不用说有空间进行粘贴)。

#1