AaronBanker
asked on
sort on column
I had this question after viewing sort on column.
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel ds.Clear" in the (Sub SortID()
Whats happening
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel
Whats happening
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Your error is due to the fact that the autofiltering is not turned on when the macro hit this instruction:
.AutoFilter.sort.SortField s.Clear
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
gowflow
.AutoFilter.sort.SortField
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A1:Z1").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
gowflow
ASKER
I have three Options Sort by Date Company and Account Code (see codes below) the above (gowflow) works when I select one option, but when i Bounce around from Company to Date to Account Code or any other combination I get an error code highlighting the
.AutoFilter.sort.SortFields.Clear
line Sub SortDataByDate()
'
' SortDataByDate Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByCompany()
'
' SortDataByCompany Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A:A").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still not working
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
ASKER
got it to work. thanks for the help
Open in new window