Hi Excel Gurus,
This is driving me crazy! This used to work until recently or someting like it. My oriignal code I was getting method apply on sort failed on every run so I made Marco3 to test with but seeing. I have a spreadsheet with a defined name ("SAV_NAME") which is linked to a activeX combobox as the ListFilledRange, SAV_NAME is defined as in the Name Manager as referring to my list of people 'Name Lookup'!$G8$:$G$2150
I'm trying to sort the data in Name Lookup using the code below. I can visually see the sort occur, however it almost immediately reverts to the pre-sorted order. I think its to do with the defined name because in the name manager it shows this description in the pre-sorted order. Is it meant to sort it in there too or is this a red herring??
I almost getting method apply of object sort failed but not all the time. Its very weird!
I've been playing around with turning off events thinking that would stop the combobox_change from triggering but that doesn't seeem to be the case.
Any help or hints apprecaited.
Sub Macro3()
'
' Macro3 Macro
'
On Error GoTo ErrorHandler
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = False
'
ThisWorkbook.Worksheets("Name Lookup").Sort.SortFields.Clear
ThisWorkbook.Worksheets("Name Lookup").Sort.SortFields.Add2 Key:=Sheets("Name Lookup").Range("G8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ThisWorkbook.Worksheets("Name Lookup").Sort
.SetRange Sheets("Name Lookup").Range("G8:I2150")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ErrorExit:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
Debug.Print Err.Number & vbNewLine & Err.Description
Resume ErrorExit
End Sub
It's also more complicated because the combo_change() routines (ther are 2 of them,one for each sheet, also doing there own sorting and these work OK. Code these these below:
Private Sub ComboBox_SAVfund_Change()
Application.Run "SORTBYFUA_AdviserData_Fund"
End Sub
Private Sub ComboBox_SAVplatform_Change()
Application.Run "SORTBYFUA_AdviserData_Platform"
End Sub
Sub SORTBYFUA_AdviserData_Fund()
' Now Sort the list of FUA on the "Adviser Data2" Single Adviser View tab from largest to smallest FUA.
With ThisWorkbook.Worksheets("Adviser Data 2")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Sheets("Adviser Data 2").Range("AH52:AH93"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 'xlSortTextAsNumbers
With .Sort
.SetRange Sheets("Adviser Data 2").Range("T52:AH93")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
''rev95 Change other SAV's to same PSN
ThisWorkbook.Worksheets("Single Adviser View - Platform").Range("AA1").Value = ThisWorkbook.Worksheets("Single Adviser View - Fund").Range("AA1").Value
ThisWorkbook.Worksheets("Single Adviser View - Fund").Activate
End Sub
Sub SORTBYFUA_AdviserData_Platform()
' Now Sort the list of FUA on the "Adviser Data" Single Adviser View tab from largest to smallest FUA.
With ThisWorkbook.Worksheets("Adviser Data")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Sheets("Adviser Data").Range("AH20:AH31"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ' xlSortTextAsNumbers 'xlSortNormal
With .Sort
.SetRange Sheets("Adviser Data").Range("T20:AH31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
''rev95 Change other SAV's to same PSN
ThisWorkbook.Worksheets("Single Adviser View - Fund").Range("AA1").Value = ThisWorkbook.Worksheets("Single Adviser View - Platform").Range("AA1").Value
ThisWorkbook.Worksheets("Single Adviser View - Platform").Activate
End Sub
This is driving me crazy! This used to work until recently or someting like it. My oriignal code I was getting method apply on sort failed on every run so I made Marco3 to test with but seeing. I have a spreadsheet with a defined name ("SAV_NAME") which is linked to a activeX combobox as the ListFilledRange, SAV_NAME is defined as in the Name Manager as referring to my list of people 'Name Lookup'!$G8$:$G$2150
I'm trying to sort the data in Name Lookup using the code below. I can visually see the sort occur, however it almost immediately reverts to the pre-sorted order. I think its to do with the defined name because in the name manager it shows this description in the pre-sorted order. Is it meant to sort it in there too or is this a red herring??
I almost getting method apply of object sort failed but not all the time. Its very weird!
I've been playing around with turning off events thinking that would stop the combobox_change from triggering but that doesn't seeem to be the case.
Any help or hints apprecaited.
Sub Macro3()
'
' Macro3 Macro
'
On Error GoTo ErrorHandler
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = False
'
ThisWorkbook.Worksheets("Name Lookup").Sort.SortFields.Clear
ThisWorkbook.Worksheets("Name Lookup").Sort.SortFields.Add2 Key:=Sheets("Name Lookup").Range("G8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ThisWorkbook.Worksheets("Name Lookup").Sort
.SetRange Sheets("Name Lookup").Range("G8:I2150")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ErrorExit:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
Debug.Print Err.Number & vbNewLine & Err.Description
Resume ErrorExit
End Sub
It's also more complicated because the combo_change() routines (ther are 2 of them,one for each sheet, also doing there own sorting and these work OK. Code these these below:
Private Sub ComboBox_SAVfund_Change()
Application.Run "SORTBYFUA_AdviserData_Fund"
End Sub
Private Sub ComboBox_SAVplatform_Change()
Application.Run "SORTBYFUA_AdviserData_Platform"
End Sub
Sub SORTBYFUA_AdviserData_Fund()
' Now Sort the list of FUA on the "Adviser Data2" Single Adviser View tab from largest to smallest FUA.
With ThisWorkbook.Worksheets("Adviser Data 2")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Sheets("Adviser Data 2").Range("AH52:AH93"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 'xlSortTextAsNumbers
With .Sort
.SetRange Sheets("Adviser Data 2").Range("T52:AH93")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
''rev95 Change other SAV's to same PSN
ThisWorkbook.Worksheets("Single Adviser View - Platform").Range("AA1").Value = ThisWorkbook.Worksheets("Single Adviser View - Fund").Range("AA1").Value
ThisWorkbook.Worksheets("Single Adviser View - Fund").Activate
End Sub
Sub SORTBYFUA_AdviserData_Platform()
' Now Sort the list of FUA on the "Adviser Data" Single Adviser View tab from largest to smallest FUA.
With ThisWorkbook.Worksheets("Adviser Data")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Sheets("Adviser Data").Range("AH20:AH31"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ' xlSortTextAsNumbers 'xlSortNormal
With .Sort
.SetRange Sheets("Adviser Data").Range("T20:AH31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
''rev95 Change other SAV's to same PSN
ThisWorkbook.Worksheets("Single Adviser View - Fund").Range("AA1").Value = ThisWorkbook.Worksheets("Single Adviser View - Platform").Range("AA1").Value
ThisWorkbook.Worksheets("Single Adviser View - Platform").Activate
End Sub