splitting an excel file

[faxof]

I have a spreadsheet with about 10000 rows of data.

The data is sorted on company name. Column C

There are about 600 different company names

I need a macro to take all the rows of data for each company and copy it to a new spreadsheet, then do the same with the next company etc etc.

many thanks
faxof

 

[ScorchedLemonade]

You can probably tackle it a bit like this. Below the several rows of data list the 600 company names. You could do this as follows - copy them down from the original list and paste into column A below your data and then in column B use something like

=if(A10002=A10003,1,0) and copy that down (supposing that the list starts at row 10002. Copy and paste by values and sort this new stuff by column B ascending. Cut off everything which has a 1 beside it. (Also cut out the zeros.) That would give you a list of the company names.
Beside that you can then do something like (in A10002 again)

=max(if(C$1:C$10000)=A10002,row(C$1:C$10000),0))

as an array formula (Ctrl-Shift-Enter).
Copy that down beside the 600 companies.
This will give you the last row that each company is in. Since the data is sorted by companies the first row will be 1 more than the last row of the previous company (except for the first company).

You could then build a macro that went something like:

First=1
Last=Cells(10002,2).Value
'Code to copy the rows from first to last
'Code to make new sheet
'Code to go to that sheet and paste
'Code to go back to the original sheet
For i=1 to 599
First=Last+1
Last=Cells(10002+i,2).Value
'the hyphenated code again
Next i

I think something like this should work. The details I left out are probably easier to formulate using the "Record Macro" facility.

Alternatively, if you have Access, you might be able to do it by exporting your sheet and making queries and then importing back to Excel. I'm not sure if that's not a lot more work though.