Modify Macro to Sort Properly

[liquidmettle]

Hi Everyone!

I found the following Macro through a search to help a co-worker sort their excel workbook. Their problem is that they have a massive amount of sheets, and they need to be in order by date.

When I went to run the macro on their sheet this morning we discovered another problem: The way the dates were written.

Right now the dates on each sheet are written as (this is just for illustration, they days won't match up):

Wednesday 1-21-15
Friday 1-1-15
Wednesday 1-2-15
Wednesday 1-12-15


Running the sort, because the days are written first, the sort will group them by day then by sequential number. The list looks like:


Friday 1-1-15
Wednesday 1-12-15
Wednesday 1-2-15
Wednesday 1-21-15

Is there any way to modify the following macro to make these sheets, as they are currently named, go in order by date?

Or is there another macro that will do the same trick?

Thank you everyone!

Please Login or Register  to view this content.

[karedog]

Add this UDF :

Please Login or Register  to view this content.

And change these lines :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mikerickson]

Change this section

Please Login or Register  to view this content.

[liquidmettle]

Hey guys thanks for the fast response!

Karedog,

I added the UDF into a second module, and changed the lines you indicated. Marco ran without issue but nothing happened.

Mike,

All three of your "LT=..." lines appear red in the VBA editor. When I delete then add back the end parentheses and hit enter, I get a "Compile error: Expected: )"

I tried running the macro regardless and I got: "Compile error: ByRef argument type mismatch" and the code that was highlighted was the "N" in the line that read "If LT(N, M) Xor....."

Any thoughts? Or could either of you make a test file to ensure I'm not messing up some transfer?

Let me know what you need form me and thanks for your help so far!

[shg]

Just remove the first opening paren on those three lines.

As an alternative, how about a macro that starts by renaming the sheets rationally -- yyyy-mm-dd. Then the sort is simple.

[karedog]

Can you upload your sample file ?

You can test with mine here :

[RFraser86]

An easy way is to make it so that the dates are in reverse - 20151201 = 2015, 12 (December), 01 (1st). That way when things are sorted by name, they are sorted by date (i.e. 20151201 comes after 20120304). Could you write a macro to basically obtain the file name dates in a column, do text to columns to split out the year, month and day, then use the "Date" formula to actually write them all into a recognisable date format - re-format those cells to yyyymmdd, then change the names of the worksheets you have so that the dates in the worksheet name are yyyymmdd....
Then in your "Sorting" macro, all you have to do is sort by name and they'll be date arranged....

[jindon]

Or is there another macro that will do the same trick?

Try

Please Login or Register  to view this content.

[liquidmettle]

Hello All,

Karedog your example file was perfect. Turns out I made the novice error of putting the Option Explicit Function in its own module.

Thanks to everyone who came to my rescue!

[karedog]

Hi liquidmettle, you are welcome, and thanks for the reputation points.


Regards