Runtime error 91

[MarianneBal]

Hello,

I just took over a report from someone with some Macros that are not working anymore.

I get for following code Runtime Error 91 - Object variable or With block variable not set.

I was reading that I have to SET something, but I'm not sure what for this code below.

Can anybody help me with that?

  Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("Z3:Z33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("K3:K33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("K3:K33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("J3:J33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("C3:C33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("D3:D33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Add Key:=Range("AB3:AB33000"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter. _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.ScreenUpdating = False
    Sheets("Projected Jurisdictional Date U").Select
    Do While IsEmpty(Range("K" & Row).Value) = False
        CheckSKU = Range("C" & Row).Value
        CheckSKU2 = Range("C" & Row + 1).Value
        CheckRev = Range("D" & Row).Value
        CheckRev2 = Range("D" & Row + 1).Value
        ActMDD = Range("AB" & Row).Value
        ActMDD2 = Range("AB" & Row + 1).Value
        If CheckRev = "A" Then
            CheckRevPrevious = "A"
        Else
            CheckRevInteger = Asc(CheckRev) - 1
            CheckRevPrevious = Chr(CheckRevInteger)
        End If
        Condition = Range("AB" & Row).Value
        Condition2 = Range("AB" & Row + 1).Value
        SalesOrgDmd = Range("K" & Row + 1).Value
        If CheckSKU Like CheckSKU2 And IsEmpty(Condition) Like IsEmpty(Condition2) And SalesOrgDmd = "Y" Then
            ActMDDPrevious = 0
            RowPrevious = 3
            SalesOrgDmdPrevious = Range("K" & RowPrevious).Value
            ConditionPrevious = Range("AB" & RowPrevious).Value
            Do While IsEmpty(Range("E" & RowPrevious).Value) = False
            
                If Range("E" & RowPrevious).Value = CheckSKU2 & "_" & CheckRevPrevious And SalesOrgDmdPrevious = "Y" And IsEmpty(ConditionPrevious) = "False" Then
                    If CheckRev = "A" Then
                        ActMDDPrevious = 0
                    Else
                        ActMDDPrevious = Range("AB" & RowPrevious).Value
                    End If
                    Exit Do
                Else
                End If
                RowPrevious = RowPrevious + 1
                SalesOrgDmdPrevious = Range("K" & RowPrevious).Value
                ConditionPrevious = Range("AB" & RowPrevious).Value
            Loop
            If CheckRev Like CheckRev2 Then
            ElseIf ActMDD2 < ActMDD And IsEmpty(Condition2) = "False" Then
                Sheets("On Hand Details").Select
                OnHandRow = 4
                Do While IsEmpty(Range("E" & OnHandRow).Value) = False
                    If Range("E" & OnHandRow).Value = CheckSKU2 & "_" & CheckRev2 Then
                        OnHandUnReInTr = Range("O" & OnHandRow).Value + Range("T" & OnHandRow).Value
                        If OnHandUnReInTr > 0 Then
                            Range("A" & OnHandRow, "Z" & OnHandRow).Select
                            Selection.Copy
                            Sheets("Batch Identification").Select
                            Range("D" & PasteRow).Select
                            ActiveSheet.Paste
                            Range("A" & PasteRow).Value = "Act MDD prior prev Rev"
                            Range("B" & PasteRow).Value = CheckRev2
                            Sheets("On Hand Details").Select
                            PasteRow = PasteRow + 1
                        Else
                        End If
                    Else
                    End If
                OnHandRow = OnHandRow + 1
                Loop
                Sheets("Projected Jurisdictional Date U").Select
            End If
            Delta = Asc(CheckRev2) - Asc(CheckRevPrevious)
            If CheckRevPrevious Like CheckRev2 And Delta < 2 Then
            ElseIf ActMDD2 < ActMDDPrevious And IsEmpty(Condition2) = "False" Then
                Sheets("On Hand Details").Select
                OnHandRow = 4
                Do While IsEmpty(Range("E" & OnHandRow).Value) = False
                    If Range("E" & OnHandRow).Value = CheckSKU2 & "_" & CheckRev2 Then
                        OnHandUnReInTr = Range("O" & OnHandRow).Value + Range("T" & OnHandRow).Value
                        If OnHandUnReInTr > 0 Then
                            Range("A" & OnHandRow, "Z" & OnHandRow).Select
                            Selection.Copy
                            Sheets("Batch Identification").Select
                            Range("D" & PasteRow).Select
                            ActiveSheet.Paste
                            Range("A" & PasteRow).Value = "Act MDD prior prev Rev"
                            Range("B" & PasteRow).Value = CheckRev2
                            Sheets("On Hand Details").Select
                            PasteRow = PasteRow + 1
                        Else
                       End If
                    Else
                    End If
                OnHandRow = OnHandRow + 1
                Loop
                Sheets("Projected Jurisdictional Date U").Select
            Else
            End If
        Else
        End If
        Row = Row + 1
    Loop
    Application.ScreenUpdating = True
    Sheets("Batch Identification").Select
    Range("A2").Select
End Sub

[alansidman]

When you run the code and get the error message, click on debug and tell us which line of code is highlighted.

[MarianneBal]

Oh yeah sorry - that would help, right?

it's this line:

    ActiveWorkbook.Worksheets("Projected Jurisdictional Date U").AutoFilter.Sort. _
        SortFields.Clear

[romperstomper]

Is there a filter set on that sheet?

[MarianneBal]

No there is no filter on that sheet.

[MarianneBal]

Oh wait - now that you were asking about the filter. I put now one on the sheet and it's working now.
I just thought he would filter automatically...?

OK, but it's working. Thanks!