auto sort?

D

Dave

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
 
S

Sunil Jayakumar

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 said:
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
Click to expand...

www.ayyoo.com/poker.html
 
D

Dave

I will try...thanks...

Sunil Jayakumar said:
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





www.ayyoo.com/poker.html
Click to expand...
 
S

Sunil Jayakumar

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
 
D

Dave

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 said:
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 said:
could you instruct me where/how to place that code?


Dave said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
S

Sunil Jayakumar

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 said:
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 said:
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 said:
could you instruct me where/how to place that code?


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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

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

could you instruct me where/how to place that code?


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



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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

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

could you instruct me where/how to place that code?


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



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
Click to expand...
Click to expand...
Click to expand...
 
S

Sunil Jayakumar

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


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

could you instruct me where/how to place that code?


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



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
Click to expand...
Click to expand...
Click to expand...
www.ayyoo.com/loans.html
 
D

Dave

yes, I agtree
thankyou so much for your help
D


Sunil Jayakumar said:
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 said:
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 said:
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


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

could you instruct me where/how to place that code?


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



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
Click to expand...
Click to expand...
www.ayyoo.com/loans.html
Click to expand...
 
Top