Auto Sort when anything in the sheet changes

[Magee]

Hi Guys

Is there a way to automatically sort the whole spreadsheet (Appart from Row 1, a header row) by Column D, and then By Column L (Descending) when Data is copied into the sheet?


Thanks

 

[Akashwani]

Hi,

This may not be the best or right solution for you, but it has bumped you back up

I don't write or understand VBA code so I am unable to help you further with this, sorry. I recorded the following macro in 2007....

Sub SortbyDthenL()
    
    Range("A1:L13").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D13") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L2:L13") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:L13")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M5").Select

End Sub

I then Right clicked the Sheet Tab, clicked on View Code and added this...

Private Sub Worksheet_Change(ByVal Target As Range)

    SortbyDthenL
    
End Sub

The sheet looked like this before I added any other data...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10	Data11	Data12
2	*	*	*	Ant	*	*	*	*	*	*	*	1
3	*	*	*	Dog	*	*	*	*	*	*	*	2
4	*	*	*	Cat	*	*	*	*	*	*	*	3
5	*	*	*	Horse	*	*	*	*	*	*	*	4
6	*	*	*	Pig	*	*	*	*	*	*	*	5
7	*	*	*	Zebra	*	*	*	*	*	*	*	6
8	*	*	*	Ant	*	*	*	*	*	*	*	7
9	*	*	*	Dog	*	*	*	*	*	*	*	8
10	*	*	*	Cat	*	*	*	*	*	*	*	9
11	*	*	*	Horse	*	*	*	*	*	*	*	10
12	*	*	*	Pig	*	*	*	*	*	*	*	11
13	*	*	*	Zebra	*	*	*	*	*	*	*	12
Sheet1

Then it looked like this after I added my name to A2.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10	Data11	Data12
2	*	*	*	Zebra	*	*	*	*	*	*	*	12
3	*	*	*	Zebra	*	*	*	*	*	*	*	6
4	*	*	*	Pig	*	*	*	*	*	*	*	11
5	*	*	*	Pig	*	*	*	*	*	*	*	5
6	*	*	*	Horse	*	*	*	*	*	*	*	10
7	*	*	*	Horse	*	*	*	*	*	*	*	4
8	*	*	*	Dog	*	*	*	*	*	*	*	8
9	*	*	*	Dog	*	*	*	*	*	*	*	2
10	*	*	*	Cat	*	*	*	*	*	*	*	9
11	*	*	*	Cat	*	*	*	*	*	*	*	3
12	*	*	*	Ant	*	*	*	*	*	*	*	7
13	Ak	*	*	Ant	*	*	*	*	*	*	*	1
Sheet1

Is that the result you would expect?
It is probably best for you to save a copy of your Workbook and then record a macro yourself as you go through the stages of sorting your data.

Good luck and hopefully an expert will see this and give you a better solution.

Ak

 

[jasonb75]

Right click your sheet tab, click view code, then copy / paste the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.Sort
         .SortFields.Clear
         .SortFields.Add Key:=Range("D2") _
              , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .SortFields.Add Key:=Range("L2") _
              , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange ActiveSheet.UsedRange
    .Header = xlYes
    .Apply
End With
End Sub