vba 将满足特定条件的数据从一张工作表复制到另一张工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18101929/
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 condition meeting data from one worksheet to another
提问by Abishek Puri
I have three worksheets, one with a list of all the cheques i write, one with a list of all cheques from my personal account and one with a list of all cheques from my business account.
我有三张工作表，一张是我写的所有支票的清单，一张是我个人账户中所有支票的清单，一张是我公司账户中所有支票的清单。
I dont know how to make all the cheques with name "Business" go to worksheet 3 and the ones with name "Personal" go to worksheet 2.
我不知道如何将所有名称为“业务”的支票转到工作表 3，将名称为“个人”的支票转到工作表 2。
Example :
例子 ：
Worksheet 1
工作表 1
Name Cheque No. Date Amount Currency
Personal 1234567 10/8/13 10000 HKD
Business 1234567 9/8/13 10000 USD
Worksheet 2
工作表 2
Name Cheque No. Date Amount Currency
Personal 1234567 10/8/13 10000 HKD
Worksheet 3
工作表 3
Name Cheque No. Date Amount Currency
Business 1234567 9/8/13 10000 USD
采纳答案by richie
Step 1:In your 'Worksheet 1' you could include one more column as 'Count' and do the following;
第 1 步：在您的“工作表 1”中，您可以再包含一列作为“计数”并执行以下操作；
Drag the formula down
向下拖动公式
Step 2:
第2步：
In 'Worksheet 2' you could do this;
在“工作表 2”中，您可以这样做；
ie For each column;
即对于每一列；
'Name' ="Personal"&ROW(P1)
'姓名' ="Personal"&ROW(P1)
'Cheque No' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,2,FALSE),"")
'支票号码' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,2,FALSE),"")
'Date' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,3,FALSE),"")
'日期' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,3,FALSE),"")
'Amount' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,4,FALSE),"")
'数量' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,4,FALSE),"")
'Currency' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,5,FALSE),"")
'货币' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,5,FALSE),"")
Drag the formulae down.
向下拖动公式。
Step 3:
第 3 步：
In 'Worksheet 3' do this;
在“工作表 3”中执行此操作；
Under the 'name' column replace 'personal' in the formula to 'business'. The rest would be the same.
在“名称”列下，将公式中的“个人”替换为“企业”。其余的都是一样的。
Make sure to drag the formulae down to get your results
确保向下拖动公式以获得结果
This is how my 'personal' (Worksheet 2) and 'business' (Worksheet 3) worksheets look like;
这就是我的“个人”（工作表 2）和“业务”（工作表 3）工作表的样子；
Sorry about the quality of images.
对图像质量感到抱歉。
回答by whytheq
Explore Siddharth and KazJaw's suggestions - they are seldom wrong.
探索 Siddharth 和 KazJaw 的建议——他们很少是错的。
A couple of common ways of tackling your problem:
解决您的问题的几种常见方法：
1.
1.
- Create a pivot table in the sheet 2.
- Create a pivot in sheet 3.
- Each pivot should have the Name field in the pivots Report Filter - set one to Personal and the other to Business.
- Use a named range in sheet 1 and set the data for each pivot to that named range.
- When new data is added you just need to adjust the named range area and press refresh.
- 在工作表 2 中创建一个数据透视表。
- 在工作表 3 中创建一个枢轴。
- 每个数据透视表的数据透视表过滤器中都应该有 Name 字段 - 将一个设置为 Personal，另一个设置为 Business。
- 使用工作表 1 中的命名范围并将每个枢轴的数据设置为该命名范围。
- 添加新数据时，您只需调整命名范围区域并按刷新。
(the last bullet point above could be a very small macro)
（上面的最后一个要点可能是一个非常小的宏）
2.
2.
Alternatively use a macro something like the following:
或者使用类似于以下内容的宏：
Option Explicit
Sub MoveToOtherSheets()
With Excel.ThisWorkbook.Sheets("Sheet1")
Dim cell
For Each cell In .Range(.Cells(2, 1), Cells(.Rows.Count, 1).End(Excel.xlUp))
If UCase(cell(1, 1)) = "PERSONAL" Then
With Excel.ThisWorkbook.Sheets("Sheet2")
cell.EntireRow.Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
If UCase(cell(1, 1)) = "COMPANY" Then
With Excel.ThisWorkbook.Sheets("Sheet3")
cell.EntireRow.Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
End With
End If
Next
End With
End Sub
