Code Help Needed to Sort Data

I am trying to figure out how to write code to look at Colum P of attached worksheet 1 & sort from lowest date to hightest date and then have information from columns A,B,C,H,J,M,N,O on Sheet 1, "Client Files" be listed on sheet 2 in that order from lowest date found in column P. I also need to have the capability of picking what dates from column P I want to select to sort. I tried doing this with a Pivot Table but I could not get the dates to sort in the correct order. The attached has the Pivot Table attached so you can see what I am talking about. Thanks.

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)))

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

Thanks. I wish I knew programming. Can you add Column D to be Phone Number and column E to be a heading of Address and have this information pulled from sheet 1. Also have column J "Lowest Next Contact Date" not show and have dates only show in columns G,H,& I when they are equal to or in between the dates a person types into cells I6 & J6. In other words, if somebody types 4/1/11 in cell I6 and 7/31 in cel J6, columns G,H,&I will populate with only dates that are equal to and fall in between 4/1/11 and 7/31/11. All other cells would be blank in that range. This would make it easier to find the dates that are due instead of seeing a bunch of dates that do not apply. I updated the spreadsheet to show you what information I wanted captured on Contact Due Dates sheet.

Hi Lynn

The attached file does what you want - I believe.
Having cleared not required dates from columns G H and I, on the Contact Due Dates sheet, there may of course be entries for those columns that do fall between the chosen dates.

The code could be modified to leave such dates in, if you wanted.

Need a bit more fine tuning on this workbook, "Dates 2" if possible. First, all the fields you have showing on sheet 2 are now correct and what I want to show. Modifications needed are as follows:

1. I had to add column I titled "HOMES" on first sheet. Not sure if this affects the code.
1. I will have up to 200 rows of data so I would need the code to look at 200 rows.
2. If column G cell shows date then show corresponding dates in colum H & I only if dates are <=30 days from column G Cell date. Otherwise leave H & I blank.
3. If column H Cell shows date, then show date in Column G if <= 30 days of H cell and show column I date if >= 30 days of H cell. Otherwise leave cells in column G & I blank.
4. If column I shows date, then show dates in column G & H if <= 30 days of I cell. Otherwise leave G & H blank.
5. Can extract data button be programmed to run system without a Macro? We have security on our system where this will be used and it always disables the Macro and I have to go into the system to enable the Macro. Most people using this are not too computer literate and may not know how to enable the system.
6. Finally, I have a 2nd workbook attached, "Dates 3" that has a slight modification from "Dates 2". Only modification is that sheet 1 of "Dates 3" has an extra column, "column K, Type of Contact" added. Not sure if this would affect the code from what is set up in "Dates2" workbook.

Thanks again for all your help.

I have made all changes I will make to this workbook. Not sure if you could make one last tweek to it and take it back to where you had it where all dates show on Contact Due Date worksheet in ascending order based off of Column R on Client Files sheet. Also, need it to go up to 200 rows. I do not want anymore fields showing on Client Files sheet than what are presently in place. Thanks.

Hi Lynn

2 new copies attached

Dates(4).xls
where the selection changes as soon as you change any dates in H8 or I8 without the user having to press Extract Data button.
You can remove that button from the sheet altogether if you wish, by right clicking the button and choosing Cut.

This version has event code on sheet Contact Due Dates which triggers the extraction

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrng As Range
    Set myrng = Range("H8:I8")
    If Not Intersect(Target, myrng) Is Nothing Then
        ExtractData
    End If
End Sub

Dates(5).xls
Has the changes you requested, but the user still has to press the Extract Data button to see the result.

In both cases, the code will deal with as many rows of data as you have on your Client files sheet. It is not restricted to 200.

This is great! Any idea why I get the error message that I get after I enter the dates? I hid some of the columns that I do not want people to see which may have caused this. The only columns that will be viewed that are not set up to view on this workbook are K & S. Some staff like to enter Type of Contact, "K" and some like to enter birthday, "S". But, the other columns I don't want them to see. I tried to look at the code you wrote to figure it out but I have no clue. Also, how did you get the worksheet lines in the second sheet to disappear? I am curious?

Hi Lynn

From a previous post

I have made all changes I will make to this workbook.

You fibbed!!!

You keep on changing the field header names.
IF
you do that, you must change the header names on sheet Client Files, to match what you have in row 10 of Contact Due Dates
AND
you must do Windows>Unfreeze Panes and make the 2 lots of header cells for each of the 3 fields match exactly the heading that you have in row 10.
THen, move down the sheet so those rows are not visible, and set Freeze Panes again.


If you don't do this, then Advanced Filter will "give up the ghost" and not play any more, with the error message you have been seeing.

Originally Posted by Lynn McCurdy

Also, how did you get the worksheet lines in the second sheet to disappear? I am curious?

I forgot to answer this in my last post.
Tools>Options>General>uncheck Gridlines