Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

vba - Excel macro to copy rows to new sheet(s) based on column value

My query is below:

Each month my telecom provider gives me an excel list of all subscribers in the following format:

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.

I've managed to go though some other queries on 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.

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

As @joseph4tw says, you could do this with a pivot table similar to below (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)

enter image description here

enter image description here

Update Having 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

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...