Auto data separation

**BRISBANEBOB** · 01-15-2009, 02:53 AM

I have a dump of 2,000 lines (running in Excel 2003) which have a column of account codes (around 100), a column of invoice numbers, and a column of values.

I want to be able to split the dump into the account codes, one per sheet. I've tried to do it with formula but failed, tried with code but failed (but as I am the worst writer of VBA in the world that wasn't suprising). Can anyone suggest the approach I should take.

A sample spreadsheet is attached.

thanks for any ideas

**royUK** · 01-15-2009, 03:12 AM

A PivotTable would be much better than splitting data into 100 sheets.

**BRISBANEBOB** · 01-15-2009, 03:23 AM

The problem with the pivot table (which I never even considered) is that I need the account users to be able to access the file and copy and paste their accounts to other applications. Is it possible to copy and paste from a pivot table?

**royUK** · 01-15-2009, 03:34 AM

It depends what you want to copy, there is also a drill down feature in a PivotTable.

I certainly would explore any option that doesn't need to generate 100 sheets which would end up with a difficult workbook to manage. I have posted several code examples to do this.

**BRISBANEBOB** · 01-15-2009, 03:42 AM

Hi Roy

Thanks for your suggestions. I'm still trying to get it to work with the individual sheets - I will probably need to be able to read out of the workbook from other workbooks. I can do it all via a horrible macro using <sort>, etc. but it's not pretty....