If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
automatic data sorting?
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. |
#2
|
|||
|
|||
automatic data sorting?
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. |
#3
|
|||
|
|||
automatic data sorting?
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. |
#4
|
|||
|
|||
automatic data sorting?
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. |
#5
|
|||
|
|||
automatic data sorting?
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. |
#6
|
|||
|
|||
automatic data sorting?
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. |
#7
|
|||
|
|||
automatic data sorting?
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. |
#8
|
|||
|
|||
automatic data sorting?
Good analogy. That is exactly what I was hoping for although I can certainly
live with the macro I have been able to create. "TRYING" wrote: 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. |
#9
|
|||
|
|||
automatic data sorting?
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Patrick" wrote in message news Good analogy. That is exactly what I was hoping for although I can certainly live with the macro I have been able to create. "TRYING" wrote: 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. |
#10
|
|||
|
|||
automatic data sorting?
I WOULD BE INTERESTED IN THIS OPTION WITH 8 COLUMNS
-- "RagDyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Patrick" wrote in message news Good analogy. That is exactly what I was hoping for although I can certainly live with the macro I have been able to create. "TRYING" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|