Data unsorts when new name added

May 28, 2013 at 13:00:42
Specs: Windows 7
Hi Guys,
I have a worksheet that has various blocks of data that are sorted on specific dates. This was kindly sorted out for me a while back. I have attached the vba that I use for this

Sub AutoSort()
  AutoSort_V3 Range("E3:K39")
End Sub

Public Sub AutoSort_V3(sortRange As Range)
  With sortRange.Parent.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortRange(1), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(2), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(3), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(4), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(5), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(6), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
Sub NewSort02()
  AutoSort_V3 Range("O3:U39")
End Sub
Sub NewSort03()
  AutoSort_V3 Range("Y3:AE39")
End Sub
Sub NewSort04()
  AutoSort_V3 Range("AI3:AO39")
End Sub
Sub NewSort05()
  AutoSort_V3 Range("AS3:AY39")
End Sub

Now this has been working absolutely fine with the set number of 'players' (Names) that I have. This is all in worksheet 'Posit'

When I add a new 'player' , and then sort the players into order in a separate worksheet with a different vba, the above become unsorted. The second vba is as follows

Sub sortplayerlist2()
'
' sortplayerlist2 Macro
'

'
    WorksheetName = ("LEGEND")
    Range("AP2:AS49").Select
    ActiveWorkbook.Worksheets("LEGEND").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LEGEND").Sort.SortFields.Add Key:=Range("AP2:AP49" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LEGEND").Sort
        .SetRange Range("AP2:AS49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    WorksheetName = ("DASHBOARD")
    Range("J2").Select
End Sub

The above sorts the list of 'players' in the worksheet titled 'LEGEND'. However when I do this the sorted information in worksheet 'Posit@ becomes unsorted. The new 'player would not have any influence on the all ready sorted data.

Is there a way that the sorted data can be locked so that if a new player is added, no effect is felt in that sorted list, or is there a more defined route to take with this problem

Please help


See More: Data unsorts when new name added

Report •


#1
May 31, 2013 at 10:59:01
Hi guys,
Been thinking about this for a couple of days and I think I have found the solution. Let me point out that once a particular range of data has been sorted , the information will not change.
The solution I have come up with is to copy and paste 'values and formats' over the original area of the worksheet, thus any change to the list of names will only affect the next range of cells to be sorted, which is kind of what I require.
The problem now, is to adapt the to following VBA's into one, of which I do need help.

Public Sub AutoSort_V3(sortRange As Range)
  With sortRange.Parent.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortRange(1), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(2), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(3), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(4), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(5), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Add Key:=sortRange(6), _
     SortOn:=xlSortOnValues, Order:=xlDescending, _
     DataOption:=xlSortTextAsNumbers
    .SetRange sortRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub


Sub NewSort06()
  AutoSort_V3 Range("BC3:BI39")
End Sub
Sub NewSort07()
  AutoSort_V3 Range("BM3:BS39")
End Sub
Sub NewSort08()
  AutoSort_V3 Range("BW3:CC39")
End Sub
Sub NewSort09()
  AutoSort_V3 Range("CG3:CM39")
End Sub
Sub NewSort10()
  AutoSort_V3 Range("CR3:CX39")
End Sub
Sub NewSort11()
  AutoSort_V3 Range("DB3:DH39")
End Sub

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("CR3:CX39").Select
    Selection.Copy
    ActiveWindow.LargeScroll Down:=-1
    Range("CR3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub


Report •
Related Solutions


Ask Question