I have a report that comes from our accounting system which it cant format
correctly, the report is split horizontally by cost centre. Like below

1041145 AgencyCharges

No Date Amount Text
data here€¦€¦€¦€¦€¦€¦€¦€¦€¦.

Transaction Summary
Totals not listed
YTD Summary

1041165 Electricity

No Date Amount Text
data here€¦€¦€¦€¦€¦€¦€¦€¦€¦.

Transaction Summary
Totals not listed
YTD Summary


The actual format of the report is not this well set up but I can pull it
into excel in this format. The problem I have encountered when trying to
format this with a macro is that the could be any number of transactions
between the heading row and the transaction summary row so I have not been
able to split this report just by row numbers.

The report eventually needs to be formatted with a row for every transaction
ie
CC Code CC Name No Date Amount Text
data here€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦€¦€¦..

I was thinking about copying each CC details onto its own sheet and then
formatting it there as I thought this would be easier to do€¦.I have detailed
how I think the code should work below but I am having trouble writing the
code.

Could anyone give me a hand writing the code below or could anyone suggest
another approach to my problem?

What I want to code to do.

Check Column A to see if it contains a value.

Keep Checking Column A until it finds another Value

Cut from First Value in Column A to Row above Second Value in column A

Paste onto New Sheet