PDA

View Full Version : Excel - auto sort linked to a separate worksheet



Karl E
22-04-2009, 01:40 AM
Hi

I'm setting up a workbook that has a Collator sheet into which lines of data are added. I want to have a series of sheets that each analyse a category of this data.

I've set up Category 1 sheet that uses IF statements to bring in the Category 1 data from the Collator sheet from lines 1 to 100. I then want to analyse this data so have set up an auto sort that collects it to a location in the Category 1 sheet in descending order.

The code used (shown below) achieves this when I type in a change to the list in the Category 1 sheet, but does not auto sort when the data is changed in the Collator sheet and feeds in via the IF formula.

I'm pretty sure I need to change the code to directly look up the range in the collator sheet, but I'm not sure how. The cell references of the range that needs to be auto sorted are the same in the Collator sheet as in the Category 1 sheet.

Any ideas?

Karl

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

'
' Manual Macro

If Target.Column <> 2 Then Exit Sub
Range("B5").Select
Range("A4:d4").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="<>"
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F108").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.sort Key1:=Range("F108"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=2
Selection.AutoFilter
Range("f107:h107").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("B4").Select
End Sub

Parry
22-04-2009, 10:12 AM
Hi, the code has been created using the macro recorder and thereís quite a few superfluous lines. A pivot table may be something you may want to investigate to summarise your data instead of code. Also the code isn't copying to another sheet - only the same sheet where the code is stored. In regards to the sorting part of the code Iíll explain what the lines are doing.

Your code

Range("F108").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.sort Key1:=Range("F108"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

The sort line has Selection.Sort which means the sorting will be based on what you have selected. When you do a sort manually you should be selecting all the cells in the range to be sorted and then choosing the column/s to sort. In your code the last cell selected was F108 so the sort is only looking at one cell Ė not the whole range.

With the macro recorder it takes a note of everything and thus uses select but itís not actually needed. Itís hard to know where this data starts and ends, but for an example lets say it starts at cell F108 and goes to the last used row in column H and you want to sort in descending order by Column F.


Range("F108:H" & Range("H108").End(xlDown).Row). Sort Key1:=Range("F108"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

hth