Excel VBA：高级筛选器将数据复制到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26407599/
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 VBA: Advanced Filter to copy data to another workbook
提问by acadie
I have a Workbook with a search interface on one Worksheet and a database on another Worksheet. this is the code i use to do an Advanced Filter on the database which returns the values on the search interface worksheet:
我有一个工作簿，一个工作表上有一个搜索界面，另一个工作表上有一个数据库。这是我用来对数据库执行高级过滤器的代码，它返回搜索界面工作表上的值：
Private Sub Find_Click()
Sheet2.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet4.Range("V1:AE2"), CopyToRange:=Sheet4.Range("E1:T1"), Unique:=False
End Sub
I am now trying to split the 2 worksheets into 2 workbooks. So the Advanced Filter would be performed on the data contained in Workbook "Data" and the results returned to the Workbook "SearchInterface". The 2 workbooks would always be opened at the same time.
我现在试图将 2 个工作表拆分为 2 个工作簿。因此，将对工作簿“Data”中包含的数据执行高级筛选，并将结果返回到工作簿“SearchInterface”。2 个工作簿将始终同时打开。
I tried inserting variables:
我尝试插入变量：
Private Sub Find_Click()
Set wbSearch = ThisWorkbook
Set wbData = Workbooks("GAL_db.xlsx")
wbData.Sheet2.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=wbSearch.Sheet4.Range("V1:AE2"), CopyToRange:=wbSearch.Sheet4.Range("E1:T1"), Unique:=False
End Sub
Getting Syntax Error on
获取语法错误
wbData.Sheet2.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy
I tried many things but nothing works. Not being too knowledgeable, i'm pretty much shooting in the dark. Any help or direction would be greatly appreciated.
我尝试了很多东西，但没有任何效果。知识渊博，我几乎是在黑暗中拍摄。任何帮助或指导将不胜感激。
==UPDATE===
==更新====
thanks for the input Rory! I changed the code as suggested to this:
感谢罗里的输入！我按照建议更改了代码：
Private Sub Find_Click()
Set wbData = Workbooks("GAL_db.xlsx")
wbData.Sheets("Sheet2").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Sheet4.Range("V1:AE2"), CopyToRange:=Sheet4.Range("E1:T1"), Unique:=False
End Sub
Now i'm getting again a syntax error with:
现在我再次收到语法错误：
wbData.Sheets("Sheet2").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy,
and Private Sub Find_Click()is highlighted in yellow...
并Private Sub Find_Click()以黄色突出显示...
采纳答案by acadie
Well, through trial and error I got it working! Yeeey! Don't know why, but it only works if I reference the Sheet4 as Worksheets(1). Here is the code that works for my purpose:
好吧，通过反复试验，我让它工作了！耶！不知道为什么，但只有当我将 Sheet4 引用为 Worksheets(1) 时它才有效。这是适用于我的目的的代码：
Private Sub Find_Click()
Dim wbData As Range
Dim wbCriteria As Range
Dim wbExtract As Range
Set wbData = Workbooks("GAL_db.xlsx").Worksheets("data").Range("A1")
Set wbCriteria = ThisWorkbook.Worksheets(1).Range("V1:AE2")
Set wbExtract = ThisWorkbook.Worksheets(1).Range("E1:T1")
wbData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wbCriteria, CopyToRange:=wbExtract, Unique:=False
End Sub
回答by Rude Dawg
I think you are looking for a combination of the 2 solutions you had...
我认为您正在寻找您拥有的两种解决方案的组合...
Private Sub Find_Click()
Set wbSearch = ThisWorkbook
Set wbData = Workbooks("GAL_db.xlsx")
wbData.Sheets("Sheet2").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=wbSearch.Sheets("Sheet4").Range("V1:AE2"), CopyToRange:=wbSearch.Sheets("Sheet4").Range("E1:T1"), Unique:=False
End Sub
