| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
auto sort?
Is there any way to make a dated entry insert itself in the worksheet in the
correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D |
|
#2
|
|||
|
|||
|
You could try the worksheet change event:
Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#3
|
|||
|
|||
|
I will try...thanks...
"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#4
|
|||
|
|||
|
could you instruct me where/how to place that code?
"Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#5
|
|||
|
|||
|
Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#6
|
|||
|
|||
|
thankyou
I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#7
|
|||
|
|||
|
Hi Dave,
You would need to enter the code to actually sort your selection. so I'm going to make the assumption that: a) The cursor will be in the last blank row (to check, before you paste this code, paste a line into your sheet, and hit Ctrl+* - this should select the entire block) b) That your date is in Column A/ Row 1 - to change this, please change the (A2) reference below to the correct column/row reference. Private Sub Worksheet_Change(ByVal Target As Range) Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1").Select End Sub You will need to paste in the entire line of new data, as this takes over as soon as any change is made to the worksheet. Do let me know if this works better for you. Cheers Sunil Jayakumar "Dave" wrote in message ... thankyou I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#8
|
|||
|
|||
|
thanks I will try
I will be away from my computer till the evening...but will let you know how it works as soon as I can thanks again D "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, You would need to enter the code to actually sort your selection. so I'm going to make the assumption that: a) The cursor will be in the last blank row (to check, before you paste this code, paste a line into your sheet, and hit Ctrl+* - this should select the entire block) b) That your date is in Column A/ Row 1 - to change this, please change the (A2) reference below to the correct column/row reference. Private Sub Worksheet_Change(ByVal Target As Range) Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1").Select End Sub You will need to paste in the entire line of new data, as this takes over as soon as any change is made to the worksheet. Do let me know if this works better for you. Cheers Sunil Jayakumar "Dave" wrote in message ... thankyou I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#9
|
|||
|
|||
|
well I gave it a try... it works well but having to paste the data in "all
at once" is a bit time consuming, but I can see that it would be hard to do it otherwise... it may be better to just record a macro to do the task? thanks again for your help! D "Dave" wrote in message ... thanks I will try I will be away from my computer till the evening...but will let you know how it works as soon as I can thanks again D "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, You would need to enter the code to actually sort your selection. so I'm going to make the assumption that: a) The cursor will be in the last blank row (to check, before you paste this code, paste a line into your sheet, and hit Ctrl+* - this should select the entire block) b) That your date is in Column A/ Row 1 - to change this, please change the (A2) reference below to the correct column/row reference. Private Sub Worksheet_Change(ByVal Target As Range) Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1").Select End Sub You will need to paste in the entire line of new data, as this takes over as soon as any change is made to the worksheet. Do let me know if this works better for you. Cheers Sunil Jayakumar "Dave" wrote in message ... thankyou I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html |
|
#10
|
|||
|
|||
|
Hi Dave,
This is pretty much the only option if you want to happen automatically - otherwise it might just be better to manually trigger the macro - using the same code, assign a shortcut key. Happy to help Sunil Jayakumar "Dave" wrote in message ... well I gave it a try... it works well but having to paste the data in "all at once" is a bit time consuming, but I can see that it would be hard to do it otherwise... it may be better to just record a macro to do the task? thanks again for your help! D "Dave" wrote in message ... thanks I will try I will be away from my computer till the evening...but will let you know how it works as soon as I can thanks again D "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, You would need to enter the code to actually sort your selection. so I'm going to make the assumption that: a) The cursor will be in the last blank row (to check, before you paste this code, paste a line into your sheet, and hit Ctrl+* - this should select the entire block) b) That your date is in Column A/ Row 1 - to change this, please change the (A2) reference below to the correct column/row reference. Private Sub Worksheet_Change(ByVal Target As Range) Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1").Select End Sub You will need to paste in the entire line of new data, as this takes over as soon as any change is made to the worksheet. Do let me know if this works better for you. Cheers Sunil Jayakumar "Dave" wrote in message ... thankyou I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html www.ayyoo.com/loans.html |
|
#11
|
|||
|
|||
|
yes, I agtree
thankyou so much for your help D "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, This is pretty much the only option if you want to happen automatically - otherwise it might just be better to manually trigger the macro - using the same code, assign a shortcut key. Happy to help Sunil Jayakumar "Dave" wrote in message ... well I gave it a try... it works well but having to paste the data in "all at once" is a bit time consuming, but I can see that it would be hard to do it otherwise... it may be better to just record a macro to do the task? thanks again for your help! D "Dave" wrote in message ... thanks I will try I will be away from my computer till the evening...but will let you know how it works as soon as I can thanks again D "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, You would need to enter the code to actually sort your selection. so I'm going to make the assumption that: a) The cursor will be in the last blank row (to check, before you paste this code, paste a line into your sheet, and hit Ctrl+* - this should select the entire block) b) That your date is in Column A/ Row 1 - to change this, please change the (A2) reference below to the correct column/row reference. Private Sub Worksheet_Change(ByVal Target As Range) Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1").Select End Sub You will need to paste in the entire line of new data, as this takes over as soon as any change is made to the worksheet. Do let me know if this works better for you. Cheers Sunil Jayakumar "Dave" wrote in message ... thankyou I pasted exactly what you posted into the worksheet ( right click sheet tab, viewcode, ) but when I enter any data I get "compile error / syntax error " i am not sure I am doing the right thing :( "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... Hi Dave, That code would need to be placed in the relevant worksheet - not in a separate module. Also, I think you would probably need to paste in all that data, as it would trigger on the first entry - so as soon as you'd inserted the data, basically. Do try this, and let me knowif it works. Sunil Jayakumar "Dave" wrote in message ... could you instruct me where/how to place that code? "Dave" wrote in message ... I will try...thanks... "Sunil Jayakumar" <sunil.jayakumar[at]gmail.com wrote in message ... You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" wrote in message ... Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D www.ayyoo.com/poker.html www.ayyoo.com/loans.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
| "-" ignored in sort | Excel Discussion (Misc queries) | |||
| Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . | Excel Worksheet Functions | |||
| Data > Sort function amnesia? | Excel Discussion (Misc queries) | |||
| Auto Sort | Excel Worksheet Functions | |||
Linear Mode
