Macro to Sort Dates

[PurrSent]

Hi, I need more help with this macro, please!

When I run ths Macro (many thanks to Osvaldo Palpeiro and shg for their help with this so far)

Sub Date_Order_1()
  '
  ' Date_Order Macro
  ' Puts Date (Column B) in ascending order
  '
  ' Keyboard Shortcut:
     
     ActiveSheet.Unprotect Password:=""
     
     With ActiveSheet
       .Unprotect
       .[A16:BZ115].Sort Key1:=[B16], Order1:=xlAscending
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     End With
        ActiveSheet.Protect Password:=""
  End Sub

Unfortunately, the whole row does not always go with the date, Sponsor etc. As a result, the amounts paid in the bank account and/or data along the row, do not alway tally with the name at the beginning of the row.

Some of the rows appear to be correct whereas others seem to have random data. Some rows even seem to have disappeared altogether

I'm hoping someone can help solve this issue, please?

I appreciate any help and advice offered. Many thanks

Here is a 'mock-up' of the spreadsheet (in principle) which I hope helps appreciate the situation

 

[yky]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Is the range, [A16:BZ115], correct? The example you gave doesn't fit this range.

 

[PurrSent]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Hi yky

The example given is just that, a 'mock up'. My apologies for not pointing that out. I just used the jpg again, after using it in another post and didn't think to mention the ranges etc.

The ranges for this ''mock up' would be different.

I've checked the range(s) I'm working on, including hidden, pre-formatted, rows and all seem to tally. I write in plural there, as there are 4 ranges, 4 Macros (ranges adjusted) and 4 buttons to activate each Macro

I'll post a section of the 'real' spreadsheet shortly (by editting this post, if it's possible)

Many thanks for interest and, hopefully, help

 

[PurrSent]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Here is a section of the 'real' spreadsheet Names have been changed, for obvious reasons. Hopefully it's more helpful than the mock-up

[/URL]free picture uploading[/IMG]

 

[yky]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Would you also post sorted result and highlight irregularities?

 

[PurrSent]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Would you also post sorted result and highlight irregularities?

This will be more difficult. I've deleted the real names and put in 'random' names to protect identity. I could do it again, with 'before' and 'after' but to cross-check will take me a little longer, obviously, so it could be a few days before I can post anything of this nature, due to other things I need to do.

I'll see what I can do ...

J

 

[PurrSent]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Hi

Here are both 'Before' and 'After' re sorting. The ones in RED are the ones that are wrong. Either the DATE, Sponsored, Gross, GA vs No GA are wrong. On the 'Before' sheet, there are three blank rows which are removed on the 'After' sheet, which is good.

The 'names' might be slightly incorrect re tally but, even so, the 'After' sheet is not correct.

I have set the 'Date' column as (Format) General and then, as previously instructed, set the column via Data > Text to Columns. Should the remaining columns also be set to 'Text to Columns' perhaps?

Hopefully, someone can correct this for me.

[/URL][/IMG]

 

[yky]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

What's wrong with row 23 of the after sheet? I assume it is also the row 23 of the before sheet.

 

[PurrSent]

Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

My mistake - I missed off the Date & Sponsor columns on the image of the 'After' sheet.

On row 23, on the 'Before' sheet, the Sponsor is 'MH' whereas on the 'After' sheet, the initials are ML

Some of the dates don't match the original dates for the Sponsors, some of the Sponsors names are missed off or changed to another name, etc. All these things seem quite random.

I've tried 'Sort Date' on another tab (less data) with smaller number of rows/columns, ie row 16 - 28; columns A - R - having copied into a new Book, then deleting columns K-P, and all seems OK but when I attempt to sort on the original sheet, it's all random mistakes again.

Many thanks for any help

Here's a better image

[/URL][/IMG]

 

[yky]

Try this:

Sub Date_Order_1()
....
       .Range("A16:BZ115").Sort Key1:=Range("B16"), Order1:=xlAscending