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

Question

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.

Answer 1

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)

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