Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Dear experts,
I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Hi Freshman,
You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Hi Shane,
Thanks for your help first. However, when I followed your instruction to copy the code and then paste it to the sheet which my data is on in VBE (A large blank space on the right navigation pane with two drop-down arrows "General" & "Declarations" on the top). When I pasted the code, the colour of the code statements changed to red. I went back to the worksheet and pasted some new records under the last record but nothing was changed. When I clicked one of cells in column A, the screen flashed back to VBE with an alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow arrow pointing to the first line of your code and the first line was also highlighted in gray. Is there anything wrong I have done so that your code was not working? Please kindly advise. Sorry for your time spent on my problem. Regards. "ShaneDevenshire" wrote: Hi Freshman, You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Freshman
You got hit by line-wrap. A couple of the lines should be all one line, not two. Try this edited version with added line-continuations "_" Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 00:14:03 -0800, Freshman wrote: Hi Shane, Thanks for your help first. However, when I followed your instruction to copy the code and then paste it to the sheet which my data is on in VBE (A large blank space on the right navigation pane with two drop-down arrows "General" & "Declarations" on the top). When I pasted the code, the colour of the code statements changed to red. I went back to the worksheet and pasted some new records under the last record but nothing was changed. When I clicked one of cells in column A, the screen flashed back to VBE with an alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow arrow pointing to the first line of your code and the first line was also highlighted in gray. Is there anything wrong I have done so that your code was not working? Please kindly advise. Sorry for your time spent on my problem. Regards. "ShaneDevenshire" wrote: Hi Freshman, You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Hi Gord,
Sorry for bother you again. Another problem came up. When I pasted new records in, an error dialogue box popped up with "Compile error: Variable not defined". When I removed the "Open Explicit", another box showed up "Compile error: Named argument not found" and the words of "DataOption1:=" in the statement were highlighted in gray. Please kindly advise how to solve this. Thanks & regards. "Gord Dibben" wrote: Freshman You got hit by line-wrap. A couple of the lines should be all one line, not two. Try this edited version with added line-continuations "_" Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 00:14:03 -0800, Freshman wrote: Hi Shane, Thanks for your help first. However, when I followed your instruction to copy the code and then paste it to the sheet which my data is on in VBE (A large blank space on the right navigation pane with two drop-down arrows "General" & "Declarations" on the top). When I pasted the code, the colour of the code statements changed to red. I went back to the worksheet and pasted some new records under the last record but nothing was changed. When I clicked one of cells in column A, the screen flashed back to VBE with an alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow arrow pointing to the first line of your code and the first line was also highlighted in gray. Is there anything wrong I have done so that your code was not working? Please kindly advise. Sorry for your time spent on my problem. Regards. "ShaneDevenshire" wrote: Hi Freshman, You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
DataOption# was added in xl2002.
This should(?) may work in previous versions: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iSect As Range Set iSect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not iSect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Freshman wrote: Hi Gord, Sorry for bother you again. Another problem came up. When I pasted new records in, an error dialogue box popped up with "Compile error: Variable not defined". When I removed the "Open Explicit", another box showed up "Compile error: Named argument not found" and the words of "DataOption1:=" in the statement were highlighted in gray. Please kindly advise how to solve this. Thanks & regards. "Gord Dibben" wrote: Freshman You got hit by line-wrap. A couple of the lines should be all one line, not two. Try this edited version with added line-continuations "_" Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 00:14:03 -0800, Freshman wrote: Hi Shane, Thanks for your help first. However, when I followed your instruction to copy the code and then paste it to the sheet which my data is on in VBE (A large blank space on the right navigation pane with two drop-down arrows "General" & "Declarations" on the top). When I pasted the code, the colour of the code statements changed to red. I went back to the worksheet and pasted some new records under the last record but nothing was changed. When I clicked one of cells in column A, the screen flashed back to VBE with an alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow arrow pointing to the first line of your code and the first line was also highlighted in gray. Is there anything wrong I have done so that your code was not working? Please kindly advise. Sorry for your time spent on my problem. Regards. "ShaneDevenshire" wrote: Hi Freshman, You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data auto sorting
Hi Dave, Gord & Shane,
Dave has solved my problem. Excellent. You three are all great. Thanks again. Have a nice day. Best regards. "Dave Peterson" wrote: DataOption# was added in xl2002. This should(?) may work in previous versions: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iSect As Range Set iSect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not iSect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Freshman wrote: Hi Gord, Sorry for bother you again. Another problem came up. When I pasted new records in, an error dialogue box popped up with "Compile error: Variable not defined". When I removed the "Open Explicit", another box showed up "Compile error: Named argument not found" and the words of "DataOption1:=" in the statement were highlighted in gray. Please kindly advise how to solve this. Thanks & regards. "Gord Dibben" wrote: Freshman You got hit by line-wrap. A couple of the lines should be all one line, not two. Try this edited version with added line-continuations "_" Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & _ Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 00:14:03 -0800, Freshman wrote: Hi Shane, Thanks for your help first. However, when I followed your instruction to copy the code and then paste it to the sheet which my data is on in VBE (A large blank space on the right navigation pane with two drop-down arrows "General" & "Declarations" on the top). When I pasted the code, the colour of the code statements changed to red. I went back to the worksheet and pasted some new records under the last record but nothing was changed. When I clicked one of cells in column A, the screen flashed back to VBE with an alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow arrow pointing to the first line of your code and the first line was also highlighted in gray. Is there anything wrong I have done so that your code was not working? Please kindly advise. Sorry for your time spent on my problem. Regards. "ShaneDevenshire" wrote: Hi Freshman, You need to use a macro: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Range("A1:A" & Range("A65536").End(xlUp).Row), Target) If Not isect Is Nothing Then Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub Put this code in the sheet module for the sheet your data is on. Press Alt F11 to open the VBE. Double-click the sheet name under your workbook name on the top left navigation pane. -- Cheers, Shane Devenshire "Freshman" wrote: Dear experts, I've a table of data and the records are filled up by rows. The new records are copied from other files and pasted to my table. Column A is for clients' numbers. What I want is whenever new records are pasted to the table, then all records will be sorted by clients' numbers in ascending order auotmatically. Please advise how it can be done. Thanks in advance. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Ranges of Data | Excel Worksheet Functions | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
How to AUTO SORT A-Z new data in a column (not menual sorting) | Excel Worksheet Functions | |||
sorting data in linked worksheets | Excel Discussion (Misc queries) |