Sorting data based on time intervals

Hi Again!

After the great success of the last macro, i wondered if it was possible to run one to sort this data.

basically i want to sort data from the first sheet, based on the Plan Ar column, i want it to be split between 2 times,

07:00 - 18:59 and then 19:00 - 06:59 - and then all the values within each time frame put into new sheets.

I tried to modify the other macro but got in a right mess! And i'm not sure if due to the formatting it is impossible.

I would greatly appreciate a hand with this, even if it is deemed impossible.

Could someone also recomed me some reading to learn macro production for myself, as i have some spare time coming up in September and would like to learn.

Many Thanks,

Will

Hi,

The first thing to do is add a helper column, say J and put the following formula in J2 and copy it down all your data rows.

=IF($C2="","",IF(AND(HOUR($B2)>=7,HOUR($B2)<19),"A","B"))

It's not clear exactly how you wish to operate the macro. Whether you want the macro to identify each range separately and sort each one or whether you want the macro to sort depending on where you have positioned the active cell. I've assumed the latter at the moment. In which case just select any of the data cells and run the following macro

Sub SortData()
    ActiveCell.CurrentRegion.Sort Key1:=Range("J:J"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1

End Sub

If you need the macro to select the various ranges and then sort them, let me know how many ranges there are likely to be, and whether they are consistently laid out. i.e. Is there always one blank row above and below the total row, and always one blank row above the data apart from the Town Name in column A. It will need a looping macro to work out where to jump to before doing the sort. Alternatively if all the ranges are named then the names themselves could be built into the macro.

HTH

Hi,

Thanks for the response, the formula alone is a great help, but i can't seem to get the macro to do anything it runs but doens't alter anything.

In response to your question, the amount of data could vary significantly, to make it easier, i will produce it with no gaps, or totals, so just a long stream of data.

What i want it to do, is to run, and create 2 new worksheets, with all the journeys that have a PlanAr or between 7:00 - 1859 and then all journeys that planar is between 19:00 and 06:59.

So in the end i will have 3 sheets, the front data sheet, a second sheet of all journeys between 07:00 and 18:59 and a third sheet with all journeys between 19:00 and 06:59.

Bascially just moving all the "A" and "B" from your formula into new sheets.

Thanks a lot.

Will

Hi,

Is the active cell somewhere in the data range before you run the macro, and have you put the formula in column J?

Attach the workbook again and I'll modify it to give you the extra two sheets.

Regards

Hi,

Cheers, i got the macro to run now, if you could get it to sort onto sepereate sheets that would be great. If you notice i have removed all line gaps, as i think that will simplyfy things?

Thanks so much for your help, sorry to take so much of your time up!

Will

Hi,

Try the attached sheet.
It's already populated, but you can remove sheets A & B and then run the macro 'CopyTimedSheets' and the data will be re-analysed

Rgds