Code Help Needed to Sort Data

**johncassell** · 05-17-2011, 05:05 AM

Hi Lynn,

What do you mean when you say 'need to have the capability of picking what dates from column P I want to select to sort' ?

Do you mean a date range (i.e 01-jan-11 to 30-Jan-11) or a list of dates (01-jan-11, 05-jan-11, 10-jan-11)?

John

**Roger Govier** · 05-17-2011, 09:48 AM

Hi Lynn

It looks as if you need Advanced Filter after you set the range of dates you want to examine.

In the attached workbook, enter the earliest date you want to retrieve in cell G6 on Sheet "Contact Due Dates", and in cell H6 enter the latest date you want to retrieve.

Then click the button Extract Data, and those records will be extracted for you and sorted in ascending order by Lowest Next Contact Date.

The code attached to the button is


Option Explicit

Sub ExtractData()
    Dim lr As Long
    Dim ws As Worksheet, wsc As Worksheet
    Dim myrange As Range
    
    Set ws = Sheets("Contact Due Dates")
    Set wsc = Sheets("Client Files")
    
    If Application.Count(ws.Range("G6:H6")) <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    lr = wsc.Cells(Rows.Count, 1).End(xlUp).Row
    wsc.Activate
    Set myrange = wsc.Range(Cells(1, 1), Cells(lr, "P"))
    ws.Activate
    
    myrange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ws.Range( _
                                                                "Crit"), CopyToRange:=ws.Range("Destination"), Unique:=False
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    If lr > 8 Then
        ws.Range("A8:H" & lr).Sort Key1:=ws.Range("H8"), Order1:=xlAscending, Header:=xlYes
    End If
    Application.ScreenUpdating = True
End Sub

You could also simplify your formula in cell M2 of Client files

FROM
=IF(I2="","",IF(J2="","",IF(J2=1,I2+7,IF(J2=2,I2+30,IF(J2=3,I2+90,IF(J2=4,I2+182,IF(J2=5,I2+365,IF(J2=6,"Case Closed",""))))))))

TO
=IF(OR(I2="",J2=""),"",IF(J2=6,"Case Closed",I2+CHOOSE(J2,7,30,90,182,365)))

**Lynn McCurdy** · 05-17-2011, 05:26 PM

I can't seem to find the extract data button that the code is tied to. Where is it?
Thanks.

**Roger Govier** · 05-17-2011, 05:55 PM

Originally Posted by Lynn McCurdy

I can't seem to find the extract data button that the code is tied to. Where is it?
Thanks.

I have made it bigger and made the text Red.
The button is located over the top of cells E5:F6

If for some reason the button isn't on your copy, use the keyboard shortcut I have assigned to the macro of Control+Shift+E

Or, Tools>Macros>highlight "Extract Data">Run