Date Format "d-mmm-yy" as Worksheet Name does not sort properly

[tv69]

Hello,

I'm pulling rows of data from a data worksheet where column 'B' contains random dates. All dates fall on the same day of the week. New worksheets are created for every new date encountered from column 'B' and the worksheet is given the name of the date in the format "d-mmm-yy", for example 1-Dec-13. The appropriate row is cut from the the data worksheet and pasted in the worksheet with the same date.

This all works well but I have a sorting issue. I found the following worksheet sorting VBA code from Microsoft's site:

Please Login or Register  to view this content.

Things work well except the date format I'm working with does not sort properly. For example if worksheets are named '1-Dec-13', '8-Dec-13', '15-Dec-13', it sorts in the order of the initial digits without respecting the actual date.

In this case the sort order result would be '1-Dec-13', '15-Dec-13', '8-Dec-13'.

I put in some more random dates to see how it behaves and this is the sort order result:

1-Dec-13, 15-Dec-13, 16-Apr-13, 3-Apr-13, 6-Jun-13, 8-Dec-13

So is it handing these names as strings not dates since the month order is being messed up too? However the date variable has been declared as 'Date' and trying to force the specified format.

The option of changing the date format is not available.

Here is my code:

Please Login or Register  to view this content.

Thank you,

TV

[Solus Rankin]

Untested. But it should sort all dates in one month? Will there be sheet names for multiple months?

Please Login or Register  to view this content.

[Kenneth Hobson]

Obviously a Worksheet object's Name value is a string. Run a macro to rename the sheet's to their numeric value, sort, and then run another or loop to rename your sheets using Format().

e.g.

Please Login or Register  to view this content.

[tv69]

Hi Solus, the dates will span over at least a couple of years. The sort behaviour should be as it is when sorting dates in any excel column in ascending or descending order.

Thanks,

TV

[LJMetzger]

Rename the Sheets to yyyy-mm-dd format, use your sorting routine, then rename the sheets back to their original names.

Please Login or Register  to view this content.

[Solus Rankin]

Okay. My code only accounted for the single month you included in your original post. Ammended:

Please Login or Register  to view this content.

[tv69]

Solus,

It errors out here with type mismatch.

Please Login or Register  to view this content.

and here:

Please Login or Register  to view this content.

It's currently not sorting correctly either.

TV

Okay. My code only accounted for the single month you included in your original post. Ammended:

Please Login or Register  to view this content.

[tv69]

Thank you Ken. I love the workarounds like that to get the desired result.

TV

Obviously a Worksheet object's Name value is a string. Run a macro to rename the sheet's to their numeric value, sort, and then run another or loop to rename your sheets using Format().

e.g.

Please Login or Register  to view this content.

[tv69]

Thank you LJ,

This method is proving to be common!

TV

Rename the Sheets to yyyy-mm-dd format, use your sorting routine, then rename the sheets back to their original names.

Please Login or Register  to view this content.

[tv69]

My error, I have a sheet in the workbook named incoming!

It sorts just fine without that sheet.

Thanks,

TV

Solus,

It errors out here with type mismatch.

Please Login or Register  to view this content.

and here:

Please Login or Register  to view this content.

It's currently not sorting correctly either.

TV

[Solus Rankin]

So is your initial issue solved?

[tv69]

It is solved I will mark the thread.

Thanks Solus.