Object variable or with block variable not set

[ApolloCarrierServ]

I have a worksheet I am trying to create a filter for. I recorded a macro with all of the filtering set to what I need. When I try to assign it to my ComboBox and run it I am getting a Run-Time Error 91 Object variable or with block variable not set. It is highlighting the section " ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Clear"

Here is my Code:

Sub FOLLOWUP()
'
' FOLLOWUP Macro
'


'
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
 ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Clear
  ActiveSheet.ShowAllData
    
    ActiveSheet.Range("$D$13:$T$1880").AutoFilter Field:=4, Criteria1:= _
        "FOLLOW UP"
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
        :=Range("P14:P1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
        :=Range("N14:N1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
        :=Range("M14:M1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
        :=Range("H14:H1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
        :=Range("E14:E1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
   
   
       End With
   End If
End Sub

Any help with this is appreciated.

 

[DanteAmor]

Try this

Sub FOLLOWUP()
'
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
[COLOR=#0000ff]        ActiveWorkbook.Worksheets("Active Leads").Sort.SortFields.Clear[/COLOR]
        ActiveSheet.ShowAllData
        ActiveSheet.Range("$D$13:$T$1880").AutoFilter Field:=4, Criteria1:= _
            "FOLLOW UP"
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
            :=Range("P14:P1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
            :=Range("N14:N1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
            :=Range("M14:M1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
            :=Range("H14:H1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort.SortFields.Add Key _
            :=Range("E14:E1880"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Active Leads").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End Sub

 

[ApolloCarrierServ]

That solved that error, but now I am getting Object doesn't support this property or method in this line. Does it have something to do with using an Active X ComboBox?

ActiveSheet.Range("$D$13:$T$1880").AutoFilter Field:=4, Criteria1:= _
            "FOLLOW UP"

 

[DanteAmor]

I tried your code and it works fine.
Apparently you have an error in each line.


Did you create the code?
Or did you get it somewhere else, and maybe it's for another version of excel?
What version of excel do you have?
Do you have Ms-office?

 

[ApolloCarrierServ]

I recorded it as a macro first then tried to attach it to the ComboBox. using this code

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then
       Select Case ComboBox1.Value


Case "FOLLOW UP": FOLLOWUP

End Select
End If
End Sub

I have Office 2007.

 

[DanteAmor]

The macro works.
You can upload the file to review.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[ApolloCarrierServ]

Thank you! I will try from a different computer and see if that works.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.