Object Variable Error

[DarrenF]

I'm trying the run the below VBA and I'm getting the below error when doing so on the below line:

ActiveWorkbook.Worksheets("Assigned Today - SD").AutoFilter.Sort.SortFields. _
Clear

Run-time error '91':
Object variable or With block variable not set

My worksheet name that I'm running the VBA on is "Assigned Today - SD", not sure how to fix the error.



Sub find_data_SD()


Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim selectdate As String
Dim selectname As String
Dim finalrow As Integer
Dim i As Integer


Set datasheet = Sheet19
Set reportsheet = Sheet18
selectdate = reportsheet.Range("B1").Value
selectname = reportsheet.Range("C1").Value


reportsheet.Range("B13:H200").ClearContents


datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To finalrow
If Cells(i, 15) = selectdate And Cells(i, 30) = selectname Then
Range(Cells(i, 31), Cells(i, 37)).Copy
reportsheet.Select
Range("B2000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
datasheet.Select
End If

Next i


ActiveWorkbook.Worksheets("Assigned Today - SD").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Assigned Today - SD").AutoFilter.Sort.SortFields. _
Add Key:=Range("G:G"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Assigned Today - SD").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With




reportsheet.Select
Range("B1").Select


End Sub

 

[Joe4]

You will get that error message if you do not actually have any filters currently on that sheet.

 

[DarrenF]

That was it, THANK YOU Joe!!!

 

[Joe4]

You are welcome.

If the issue is that sometimes there may be filters, and other times not, what people often do is simply tell Excel to ignore that error, i.e.

On Error Resume Next
ActiveWorkbook.Worksheets([COLOR=#333333]"Assigned Today - SD"[/COLOR]).AutoFilter.Sort.SortFields. _
Clear
On Error GoTo 0

So then it will just continue on to the next step without posting any errors to the screen.

 

[DarrenF]

Thank you for the additional information.