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.SortFields.Clear" in the (Sub SortID()

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

Open in new window



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
    

Open in new window

AaronBankerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
With Sheets("Expenses").Sort
    .SortFields.Clear
    'etc

Open in new window

gowflowCommented:
Your error is due to the fact that the autofiltering is not turned on when the macro hit this instruction:
.AutoFilter.sort.SortFields.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:

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

Open in new window



gowflow
AaronBankerAuthor Commented:
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

Open in new window

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

Open in new window

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

gowflowCommented:
Simply put this
.Range("A1:Z1").AutoFilter

After this line
ActiveWindow.SmallScroll ToRight:=3


gowflow

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AaronBankerAuthor Commented:
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
AaronBankerAuthor Commented:
got it to work.  thanks for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.