VBA中的Excel过滤和复制 [英] Excel Filtering and Copying in VBA

问题描述

我正在开发一个 VBA 脚本，该脚本从 Access 中提取一系列日期，然后过滤数据并根据过滤后的数据创建图表.过滤后的数据将转到单独的工作表，图表将从中提取其数据.我可以使用 SQL 语句从 Access 中获取数据，但是我在 Excel 中的 AutoFilter 出错了.这是我所拥有的...

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 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 Next 用于当没有过滤器可以跳过错误时.请注意 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过滤和复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！