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.

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

--
RyGuy


"Patrick" wrote:

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.

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!

"ryguy7272" wrote:

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

--
RyGuy


"Patrick" wrote:

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.

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" wrote:

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

--
RyGuy


"Patrick" wrote:

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.

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



--
RyGuy


"Patrick" wrote:

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" wrote:

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

--
RyGuy


"Patrick" wrote:

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.

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.
"ryguy7272" wrote:

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



--
RyGuy


"Patrick" wrote:

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" wrote:

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

--
RyGuy


"Patrick" wrote:

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.

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" wrote:

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.
"ryguy7272" wrote:

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



--
RyGuy


"Patrick" wrote:

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" wrote:

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

--
RyGuy


"Patrick" wrote:

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.