PDA

View Full Version : [SOLVED] Sort Automatic Sheet based other sheet



elsg
11-27-2013, 03:37 PM
hellow
how to sort data in ("SortSheet"), when any data is changed on the tab ("UpDateTab")?



Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("UpDateTab").UsedRange.Copy Worksheets("SortSheet").Range("C7")
ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Add Key:=Range("C7:R5000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("SortSheet").Range("C7:R5000").Sort

.SetRange Range("C7:R5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With


End Sub
I have to clean the cells of the guide ("SortSheet").
then copy the data tab ("UpDateTab"), Column C7:. R5000, then paste the data into ("SortSheet") Column C7: R5000
After all this, I need to sort the range ("SortSheet") Column C7:. R5000


the code must be run every time data is changed in any ("UpDateTab")
Cross-Post
http://www.mrexcel.com/forum/excel-questions/741818-sort-automatic-sheet-based-other-sheet.html

Thank you!

mancubus
11-27-2013, 03:59 PM
hi.

this is what macro recorder gives me.



Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("UpDateTab").UsedRange.Copy Worksheets("SortSheet").Range("C7")

Worksheets("SortSheet").Sort.SortFields.Clear
Worksheets("SortSheet").Sort.SortFields.Add Key:=Range("C7"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets("SortSheet").Sort
.SetRange Range("C7:R5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

elsg
11-27-2013, 04:12 PM
very good!!!!!!

thank you very much!!!!!!!