问题描述
我正在使用VBA脚本，从Access中提取一系列日期，然后过滤数据，并根据过滤后的数据创建一个图表。过滤后的数据将转到单独的表格，其中图表将从中拉出其数据。我可以使用SQL语句从Access获取数据，但是我的AutoFilter在Excel中是错误的。这是我有...
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 seperate sheet where the chart will be pulling its data from . I can get data out of Access with a SQL statement, but my AutoFilter in Excel is erroring out. Here is what I have...
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：这个过滤器会发生有22台独特的机器，所以我计划为每个机器运行循环。如果这不是最快或正确的方式，请让我知道。
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.
推荐答案
您需要将其分为两部分。过滤然后复制/粘贴。请参见下文：
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
删除过滤器：
On Error Resume Next
Sheet3.ShowAllData
On Error GoTo 0
On Error Resume下一个是当没有过滤器跳过错误时。请注意使用Sheet3和Sheet2来寻找通用解决方案。
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.
这篇关于在VBA中进行Excel过滤和复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！