vba 在excel中查找结果将行复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14731185/
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
Find Results in excel copy the rows to another sheet
提问by amateur
I need some help copying data from one excel worksheet to another. For example:
我需要一些帮助将数据从一个 Excel 工作表复制到另一个。例如：
Sample Data
样本数据
__A__B___C
__一__B___C
1 aaa bbb ddd
1 aaa bbb ddd
2 bbb ccc eee
2 bbb ccc eee
3 ccc fff rrr
3 ccc fff rrr
4 ccc fff ttt
4 ccc fff ttt
5 ddd eee ggg
5 ddd eee ggg
6 aaa ddd eee
6 aaa ddd eee
7 bbb fff hhh
7 bbb fff hhh
8 eee eee eee
8 呜呜呜呜
So for the above records if I do a 'CTRL + F' and search to find All 'eee' the results will show 6 instances in C2, B5, C6, A8, B8, C8
因此，对于上述记录，如果我执行“CTRL + F”并搜索以查找所有“eee”，结果将在 C2、B5、C6、A8、B8、C8 中显示 6 个实例
Now I want to copy the whole of rows 2,5,6 and 8 to another worksheet.
现在我想将整个第 2、5、6 和 8 行复制到另一个工作表。
回答by MarkAust
This worked for me... a simple solution:
这对我有用......一个简单的解决方案：
- Select/highlight the data you want to search
- Press ctrl +h for Replace
- Enter the string you want to find in "Find What".
- Select "Replace with" Format, then Format > Fill and choose a background fill, doesn't matter what color. Then click [Replace All].
- Then choose on the Excel menu > Data > Sort on "Cell Color", Order (the color you chose from step 4 above) and then "On Top" or "On Bottom" - whatever you want, click [Ok].
- 选择/突出显示要搜索的数据
- 按 ctrl +h 进行替换
- 在“查找内容”中输入要查找的字符串。
- 选择“替换为”格式，然后格式> 填充并选择背景填充，不管是什么颜色。然后点击【全部替换】。
- 然后选择 Excel 菜单 > 数据 > 按“单元格颜色”排序，顺序（您从上面第 4 步中选择的颜色），然后选择“在顶部”或“在底部” - 无论您想要什么，单击 [确定]。
Now all the data you wanted is all in one block of rows. Copy and paste to another worksheet as required.
现在您想要的所有数据都在一行行中。根据需要复制并粘贴到另一个工作表。
回答by Peter L.
To achieve what you need please do the following (assuming your data is located in columns A:C starting row 2, i.e. there are headers):
要实现您的需求，请执行以下操作（假设您的数据位于 A:C 列中，从第 2 行开始，即有标题）：
- Type in D2 (additional column):
=IF(OR(A2:C2="eee"),1,0), but press CTRL+SHIFT+ENTERinstead of usual ENTER- this will define an ARRAY formula and will result in{}brackets around it (but do NOT type them manually!). - Autofill formula as required.
- Add filter to the whole A:D range.
- Apply filter to column D for value
1. - Copy entire filtered columns A:C and paste anywhere - only filtered rows will be copied.
- 输入 D2 (additional column):
=IF(OR(A2:C2="eee"),1,0)，但按CTRL+ SHIFT+ENTER而不是通常的ENTER- 这将定义一个 ARRAY 公式并{}在它周围产生括号（但不要手动输入它们！）。 - 根据需要自动填充公式。
- 将过滤器添加到整个 A:D 范围。
- 将过滤器应用于列 D 的 value
1。 - 复制整个过滤的列 A:C 并粘贴到任何地方 - 只会复制过滤的行。
Sample file: https://www.dropbox.com/s/qscwbf5kbnwi5pa/Filtering3Columns.xlsx
示例文件：https: //www.dropbox.com/s/qscwbf5kbnwi5pa/Filtering3Columns.xlsx
回答by Ekonion
I think what you are looking for is called VLOOKUPjust take the Excel-help, theres everything explained
我想你要找的东西叫做VLOOKUPExcel 帮助，一切都解释了
回答by Teodor
The easiest way I found was this: Using the option "Sort & Filter" located in the right upper corner. Then you can filter any column by any criteria you like. By doing that only the filtered rows appear on the spread sheet (the rest are still there but just hidden). From there you can easily copy and paste the entire rows.
我发现的最简单的方法是：使用位于右上角的“排序和过滤”选项。然后您可以按您喜欢的任何条件过滤任何列。通过这样做，只有过滤的行出现在电子表格上（其余的仍然存在，但只是隐藏了）。从那里您可以轻松地复制和粘贴整行。
