vba Excel 宏可根据列值将行复制到新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12119660/
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 macro to copy rows to new sheet(s) based on column value
提问by user1624085
My query is below:
我的查询如下：
Each month my telecom provider gives me an excel list of all subscribers in the following format:
每个月，我的电信提供商都会以以下格式向我提供所有订阅者的 Excel 列表：
GSM number, account num, caller location, called location, units, cost
123456, abc123456, loc1, loc2, 23, 11.231
123456, abc123456, loc8, loc4, 5, 3
123456, abc123456, loc1, loc4, 102, 71.1
789012, def789012, loc4, loc5, 11, 1.43
789012, def789012, loc4, loc5, 1, 0.23
345678, ghi345678, loc6, loc7, 7, 2.23
and so on..
等等..
I'd want to go through the column A (gsm number) and copy all rows for that number to a worksheet named for the number, e.g. rows 1-3 would be copied to a worksheet named 123456.
我想通过 A 列（gsm 号码）并将该号码的所有行复制到以该号码命名的工作表中，例如第 1-3 行将被复制到名为 123456 的工作表中。
I've managed to go though some otherquerieson this site and come up with something that works, but doesn't. The problems I'm facing is that the number of rows for a particular caller are not fixed (one row per call) and the number of callers are not fixed either (zero calls means no rows). I'm trying for a OnCellValueChange type solution - since I can understand & edit macros - but haven't written one yet.
我已经设法通过本网站上的其他一些查询并提出了一些有效的方法，但没有。我面临的问题是特定调用者的行数不固定（每次调用一行）并且调用者的数量也不固定（零调用意味着没有行）。我正在尝试一种 OnCellValueChange 类型的解决方案 - 因为我可以理解和编辑宏 - 但还没有写一个。
Thanks for your time!
谢谢你的时间！
edit: also, as a side-note, can I also put a total of the "cost" column on each spreadsheet?
编辑：另外，作为旁注，我还可以在每个电子表格上添加“成本”列的总数吗？
Edit Edit: Thanks to all for your solutions. However, my uncomfortability with VBA was hampering me. So, I'm just taking the excel sheet as-is in an SQL database and doing all of the formatting and querying in SQL/ASP directly online. So now users simply enter their phone # (and a password) and see their call records with dynamically generated subtotals and sortings. Thanks y'all for your very kind help.
编辑 编辑：感谢大家的解决方案。然而，我对 VBA 的不舒服阻碍了我。所以，我只是在 SQL 数据库中按原样使用 Excel 工作表，并直接在线进行 SQL/ASP 中的所有格式化和查询。因此，现在用户只需输入他们的电话号码（和密码），即可查看带有动态生成的小计和排序的通话记录。谢谢大家的帮助。
回答by lori_m
As @joseph4tw says, you could do this with a pivot table similar to below (Excel 2010)...
正如@joseph4tw 所说，您可以使用类似于以下（Excel 2010）的数据透视表来执行此操作...
To show all item labels, choose: Field Settings -> Layout & Print -> Repeat Item Labels
To create the sheet tabs just select Pivot Table Options -> Show Report Filter Pages(as below)
要显示所有项目标签，请选择：字段设置 -> 布局和打印 -> 重复项目标签
要创建工作表选项卡，只需选择Pivot Table Options -> Show Report Filter Pages（如下所示）
UpdateHaving created the sheet tabs, you can use group editing to format as required...
更新创建工作表选项卡后，您可以使用组编辑来根据需要设置格式...
- select all the tabs by shift-clicking the last tab,
- select the whole sheet and paste special values to remove all pivot tables
- delete the first two rows and autosize columns as shown
- 通过按住 Shift 键单击最后一个选项卡来选择所有选项卡，
- 选择整个工作表并粘贴特殊值以删除所有数据透视表
- 删除前两行并自动调整列的大小，如图所示
