Sort Macro?

[Mashtonsmith]

I am in need of help with the following problem.
The sample below details data which needs to be split out into ideally separate workbooks or at least worksheets by - COST CENTRE.

There may be one set of data per cost centre or many so one worksheet could be small, the next quite large.

The cost cente in the first example BS911 is in column B. I can't sort by column B as it will drop all of the information below.
There are almost 700 different cost centres. Is it possible to sort by all of these then split them out into separate worksheets - by macro if at all possible.

COST CENTRE - BS911 1,333.02 1,237.27 11,668.45 17,778.30 29,749.30
¤
METD02(R02) STAFF PAYROLL COSTS - SUMMARY - MONTHLY - COMBINED OVERTIME #NAME? RIOD 06 23/09/2005 PAGE 7
 NAME HED PREMIUM HOURS OVERTIME HOURS PREMIUM COST OVERTIME COST TOTAL COST PAYPOINT
 NO

COST CENTRE - CA300
GRADE - D00001 #NAME?
 ADMN 27814 924 SMITH, Mr R J 160 5 77.94 77.94 CA3A
 ADMN 27814 924 SMITH, Mr R J 161 5 27.57 27.57 CA3A
 ADMN 27814 924 SMITH, Mr R J 162 6.15 6.15 137.83 137.83 275.66 CA3A
 ADMN 27814 924 SMITH, Mr R J 166 6.15 6.15 137.83 137.83 275.66 CA3A
 ADMN 27814 924 SMITH, Mr R J 170 0.3 5.51 5.51 CA3A
 ADMN 27814 924 SMITH, Mr R J 171 0.25 0.5 6.89 13.78 20.67 CA3A
 ADMN 27814 924 SMITH, Mr R J 176 1.15 1.15 27.57 27.57 55.14 CA3A
 ADMN 27814 924 SMITH, Mr R J 182 2.3 55.14 55.14 CA3A

GRADE - D00001 #NAME? 19.1 22.35 337.69 455.6 793.29
COST CENTRE - CA300 19.1 22.35 337.69 455.6 793.29
¤
METD02(R02) STAFF PAYROLL COSTS - SUMMARY - MONTHLY - COMBINED OVERTIME #NAME? RIOD 06 23/09/2005 PAGE 8
 NAME HED PREMIUM HOURS OVERTIME HOURS PREMIUM COST OVERTIME COST TOTAL COST PAYPOINT
 NO

COST CENTRE - CA400
GRADE - C00001 - BAND C
 ADMN 62618 728 JONES, Mr F L 161 8 54.44 54.44 CA4A
 ADMN 62618 728 JONES, Mr F L 162 4 4 108.87 108.87 217.74 CA4A
 ADMN 62618 728 JONES, Mr F L 171 0.4 1.2 13.61 27.22 40.83 CA4A
 ADMN 62618 728 JONES, Mr F L 176 1 1 27.22 27.22 54.44 CA4A
 ADMN 62618 728 JONES, Mr F L 185 10 136.09 136.09 CA4A

 

[Harvey]

you can use a macro to fill out the cost centre # in every row so that you can eventually sort on cost centre.
I'm not completely sure though of how you want to split the data. Does all the data below a cost centre "belong" to that cost centre? Can a cost centre appear more than once on a sheet? Can you post an example of your sheet using the html maker?

 

[Mashtonsmith]

Yes all of the data below a cost centre belongs to that one centre. A cost code only occurs once within the master sheet.

Unsure how to attach in HTML. Tried before but clearly it didn't paste well.

 

[Harvey]

You have to download the html maker and insert it into excel as a plugin. Then a menu is added to excel where you can make html source of a selection.

 

[Mashtonsmith]

Unfortunately I can't load plugins due to network restrictions at my work.