automatic data sorting?

[Patrick]

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

 

[ryguy7272]

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

 

[Patrick]

Thank you. I am not at all savy or familiar with macros but it appears to be
time I learned. I wil give this a shot and get back to you!

 

[Patrick]

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

 

[ryguy7272]

Right-click on the tab and select 'View Code'. Paste the code I gave you
right in there...

 

[Patrick]

Thanks again. I did that and must be missing something. Let me tell you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all kinds of
different dates, hit control s and it sorts everyting for me. The missing
component is to get this to happen without me having to use control s. I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out what.

Anyway, I did right click on the tab and pasted your stuff. I changed the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an earlier
date at the end of the column and hoped it would aytomatically be sorted. It
didn't happen and I am sure I am missing something I was supposed to have
done.

I was wondering if I could edit the macro that I "recorded" with the macro
recorder that would tell it to run automatically without using the hot key.
No rush getting back to me and I do appreciate all of your help.

 

[TRYING]

Please know that I am here mostly to observe and learn. I have very little to
contribute, if any, by way of solutions to problems posted here.

From my limited experience with Excel, and even less with macros, I would
hazard a guess that what you are wanting is something like having a
motionsensor controlled room light that turns on without you having to so
much as flick a switch. And I would liken your pressing Ctrl + s as switching
on the room light by flicking a switch. That is if I understand what you want
done as Excel automatically sorting your list as soon as you input the
previously forgotten entry without you having to do anything else.

 

[Patrick]

Good analogy. That is exactly what I was hoping for although I can certainly
live with the macro I have been able to create.

 

[RagDyer]

Your dates can be easily "auto" sorted in another column using formulas.
The question is, how many other columns (fields) are you looking to sort
along with the dates, and ... are there duplicate dates?

The formulas could be in the actual "presentation" cells, while your data
entry itself, is in an out-of-the-way location.

Post back if you might be interested in pursuing this type of procedure.

 

[bkunes]

I WOULD BE INTERESTED IN THIS OPTION WITH 8 COLUMNS

 

[RagDyeR]

You didn't answer the 2nd half of the question ... are there duplicate
dates?

And also, posting in all caps is considered SHOUTING, and regarded by many
to be rude.


--

Regards,

RD

 

[bkunes]

sorry about the all caps i use it in auto cad, and yes i have multiple dates,
i appreciate the help

 

[RagDyeR]

OK, BUT let's switch to your last post in the *newest* thread.
--

Regards,

RD