vba VBA中的Excel过滤和复制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13955708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Filtering and Copying in VBA
提问by Grant
I'm working on a VBA script that pulls a range of dates from Access, then filters the data and creates a chart based on the filtered data. The filtered data will be going to a separate sheet where the chart will be pulling its data from . I can get data out of Access with a SQL statement, but my AutoFilterin Excel is erroring out. Here is what I have...
我正在处理一个 VBA 脚本，该脚本从 Access 中提取一系列日期，然后过滤数据并根据过滤后的数据创建图表。过滤后的数据将转到单独的工作表，图表将从中提取其数据。我可以使用 SQL 语句从 Access 中获取数据，但我AutoFilter的 Excel 出错了。这是我所拥有的...
Sheet3.Range("F4:F500").AutoFilter(, "Riveter 01").Copy Destination:=Sheet2.Range("A5")
It gives an app-defined or object-defined error and I can't figure out why. Is this the proper way or is there an easier way?
它给出了应用程序定义或对象定义的错误，我不知道为什么。这是正确的方法还是有更简单的方法？
PS: This filter will happen with 22 unique machines so I was planning on running a loop for each machine. If that is not the fastest or proper way please let me know.
PS：这个过滤器将在 22 台不同的机器上发生，所以我计划为每台机器运行一个循环。如果这不是最快或正确的方法，请告诉我。
回答by InContext
You need to split this into two parts. Filter and then copy/ paste. See below:
你需要把它分成两部分。过滤然后复制/粘贴。见下文：
With Sheet3
.AutoFilterMode = False
With .Range("F4:F500")
.AutoFilter Field:=1, Criteria1:="Riveter 01"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A5")
End With
End With
to remove the filter:
删除过滤器：
On Error Resume Next
Sheet3.ShowAllData
On Error GoTo 0
On Error Resume Next is for when there is no filter present to skip the error. Please note the use of Sheet3 and Sheet2 for those looking for a generic solution.
On Error Resume Next 适用于没有过滤器可以跳过错误的情况。请注意对于那些寻找通用解决方案的人使用 Sheet3 和 Sheet2。
回答by PaulStock
I think that you have to do this in 2 separate steps:
我认为您必须分 2 个单独的步骤执行此操作：
- filter the data
- copy the filtered data to another sheet
- 过滤数据
- 将过滤后的数据复制到另一个工作表
The answer here has an excellent example of how to do this: Autofilter Macro, then copy visible data ONLY and paste to next available row
这里的答案有一个很好的例子来说明如何做到这一点：自动过滤宏，然后仅复制可见数据并粘贴到下一个可用行
