I have a simple macro that I made with the "record macro" feature that pulls in data from a different tab and then paste is as values to remove all links and then sorts it by one of the columns. For some reason I keep getting an error that says
Run Time Error '91:
Object variable or With block variable not set
When I hit the "debug" error it highlights this part of the code:
ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort. _
SortFields.Clear
The entire macro is below. Any ideas on what I did wrong or how to fix? Thank you in advance!
Sub Closest_Expiration_Dates()
'
' Closest_Expiration_Dates Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Sheets("Condensed NSAs").Select
Selection.Copy
Sheets("Closest Expiration Dates").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:R1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("R1"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("R1"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Closest Expiration Dates").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-3
End Sub
Bookmarks