Macro Magic Required

[mikemcbain]

I use Office 365 and xlsx and xlsm and the occasional old xls file.
I have a large xls workbook which includes Sheets called Today and Best.
On my Today sheet which changes each day I have Columns E to CH sorted by Column Z which has dates in descending Date order, somedays just one Row per Date but somedays there can be up to 20 Rows with the same Date and perhaps 50 different dates.Column N called ARATE is a secondary sort column.
Now here is my problem....I want to copy only the first Row of each date to my worksheet called Best.
I will attach a before and after screen shot
It is so tedious doing it manually.
With thanks for your patience and assistance.
Old Mike.

 

[mikemcbain]

Great!


In that case I would suggest this which removes the 'Best' sheet (code assumes that sheet does exist) and duplicates 'Today' which would preserve all formatting, column widths etc before removing the required rows all at the one time on the new 'Best' sheet.

Sub FirstDateOnly_v2()
  Application.DisplayAlerts = False
  Sheets("Best").Delete
  Application.DisplayAlerts = True
  Sheets("Today").Copy After:=Sheets("Today")
  With ActiveSheet
    .UsedRange.RemoveDuplicates Columns:=26, Header:=xlYes
    .Name = "Best"
  End With
End Sub

Thanks again Peter.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

BTW, you have a post marked as the accepted solution that is clearly not a solution. Perhaps you meant to mark that one with a 'Like'? ?
So you should at least unmark that one but then you can decide yourself if you want to mark one of the others as the accepted solution.

 

[Michael M]

@Peter_SSs
I don't think there will ever be a solution for being 68.......except being 69.....