VBA 高级过滤唯一值并复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32787227/
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
VBA Advanced filter unique values and copy to another sheet
提问by squar_o
I've tried a number of methods to filter a column for unique values (including blanks and blanks with a space inside). I want to filter these values and then paste them into column a in another sheet.
我尝试了多种方法来过滤一列的唯一值（包括空白和内部有空格的空白）。我想过滤这些值，然后将它们粘贴到另一个工作表的 a 列中。
Sub filter_2()
With Sheets("ICM flags")
.Columns("a").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Flag Update (2)").Range("a2"), Unique:=True
End With
End Sub
The method above works for .range("a2")but if I try to use .range("a1")I get runtime error 1004 advanced filter method of class failed.
上述方法适用，.range("a2")但如果我尝试使用，.range("a1")我会收到运行时错误 1004 类的高级过滤器方法失败。
I get runtime error 1004 advanced filter method of class failed with the method below.
我收到运行时错误 1004 类的高级过滤器方法失败，使用以下方法。
Sub unique_filter()
Sheets("ICM flags").Columns("a").AdvancedFilter _
Action:=xlFilterCopy, _
copytorange:=Sheets("Flag Update (2)").Range("a1"), Unique:=True
End Sub
For both of the above methods and with the edits to Davesexcel answer:
对于上述两种方法以及对 Davesexcel 答案的编辑：
Sub AdvFilter()
Dim ws As Worksheet, sh As Worksheet
Dim rng As Range, Frng As Range
Set sh = Sheets("ICM Flags")
Set Frng = sh.Range("A:A")
Set ws = Sheets("Flag Update (2)")
Set rng = ws.Range("A1")
'Frng.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng, Unique:=True
Frng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub
The method works for FilterInPlacebut not CopyToRange
该方法适用于FilterInPlace但不适用CopyToRange
回答by Davesexcel
When I ran your code, it worked with no errors on my end.
当我运行你的代码时，它在我的最后没有错误。
This is the same code but using variables and might work better for you.
这是相同的代码，但使用变量，可能更适合您。
Sub AdvFilter()
Dim ws As Worksheet, sh As Worksheet
Dim rng As Range, Frng As Range
Set ws = Sheets("Flag Update (2)")
Set rng = ws.Range("A1")
Set sh = Sheets("ICM flags")
Set Frng = sh.Range("A:A")
Frng.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng, Unique:=True
End Sub
回答by squar_o
It seems the problem was being caused by trying to use CopyToRangeto copy to another sheet. I have worked around this by first copying to the same sheet then copying these values to the sheet I want them on
问题似乎是由于尝试使用CopyToRange复制到另一张纸引起的。我已经解决了这个问题，首先复制到同一张纸，然后将这些值复制到我想要它们的纸上
Sub AdvFilter() 'filters flags copied from ICM to unique values and pastes into Flag update sheet.
Dim ws As Worksheet, sh As Worksheet
Dim rng As Range, Frng As Range, Prng As Range
Set ws = Sheets("Flag Update (2)")
Set sh = Sheets("ICM Flags 1")'destination sheet
Set Frng = sh.Range("A:A")'filter range
Set rng = sh.Range("c1")'filter output range
Frng.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng, Unique:=True
ws.Columns("a").Value = sh.Columns("c").Value 'Separate copying as the filter didn't like copying to another sheet
End Sub
回答by user2370018
Actually The destination needs to be made the active sheet, then you can get unique values from any other sheet or workbook
实际上，目的地需要成为活动工作表，然后您可以从任何其他工作表或工作簿中获取唯一值
