vba 将特定数据从一张纸复制到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12932741/
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
Copying specific data from one sheet to another
提问by davey
2 columns on master sheet:
主表上的 2 列：
col1 col2
Apple Fruit
Spinach Veg
Orange Fruit
Potatoe Veg
Pear Fruit
Bannana Fruit
Carrot Veg
Potataoe Veg
Say I wanted to copy all Fruitdata to a another sheet and all Vegdata to a 3rd sheet. Is there a way to do this? Does it require a macro or can I somehow do a VLOOKUP?
假设我想将所有Fruit数据复制到另一个工作表，并将所有Veg数据复制到第三个工作表。有没有办法做到这一点？它需要一个宏还是我可以做一个VLOOKUP？
Also I need this to be dynamic, so if a new row is added the corresponding sheets are updated.
另外我需要它是动态的，所以如果添加新行，相应的工作表就会更新。
回答by Stepan1010
You could use this horrible monstrosity of a formula:
你可以使用这个可怕的公式：
=INDEX($B:$B, SMALL(IF("veg"=$A:$A, ROW($A:$A)-MIN(ROW($A:$A))+1, ""), ROW(A1)))
Enter it as an array formula with Ctrl+Shift+Enter.
使用 Ctrl+Shift+Enter 将其作为数组公式输入。
I found the formula here: http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/
我在这里找到了公式：http: //www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/
Which might not be the best version or most elegant version of this formula(it is a common formula task) but it will get the job done.
这可能不是这个公式的最佳版本或最优雅的版本（这是一个常见的公式任务），但它会完成工作。
回答by pnuts
I'd use PivotTables (using Excel 2007) but you will need to refresh each time the source is modified/extended and to keep an eye on Table/Data Range: not stopping short of any additions:
我会使用数据透视表（使用 Excel 2007），但每次修改/扩展源时都需要刷新并密切关注表/数据范围：不要停止添加任何内容：
回答by brettdj
- right click your sheet tab
- View Code
- copy and paste in the code below
- 右键单击您的工作表标签
- 查看代码
- 复制并粘贴下面的代码
whenever column B is changed the code
每当 B 列更改代码时
- AutoFilters column B for "Fruit", then copies records to sheet 2 columns A:B
- AutoFilters column B for "Veg", then copies records to sheet 2 columns A:B
- 为“水果”自动筛选 B 列，然后将记录复制到工作表 2 列 A:B
- 为“Veg”自动筛选 B 列，然后将记录复制到工作表 2 列 A:B
Code
代码
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set rng1 = Intersect([B:B], Target)
If rng1 Is Nothing Then Exit Sub
Set ws1 = Sheets(2)
Set ws2 = Sheets(3)
ws1.[A:B].ClearContents
ws2.[A:B].ClearContents
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A:B")
.AutoFilter Field:=2, Criteria1:="Fruit"
.Copy ws1.[a1]
.AutoFilter Field:=2, Criteria1:="Veg"
.Copy ws2.[a1]
End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
