Im getting an Application defined or Object defined Error on a macro

[Phantasm]

I have no idea why I am getting an error on this. It was working fine earlier. The error happens on the line that is bold.

Sub SHIPJOB(control As IRibbonControl)

With Sheets("JOBS IN PROCESS NEW")

If .AutoFilterMode Then .AutoFilterMode = False

.Range("A1:O1").AutoFilter 15, "x"

.AutoFilter.Range.Offset(1).Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)

.AutoFilter.Range.Offset(1).EntireRow.Delete

.AutoFilterMode = False

If Not Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter Then

Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter

End If

ActiveWorkbook.Sheets(3).Activate

Rows("2:150").RowHeight = 16.5

End With

End Sub

 

[Yongle]

Your code does not fail for me
Does sheet "Shipped Orders" exist? Is the spelling correct? (think it would give a different error in any case)
Test this above the line that fails

MsgBox .AutoFilter.Range.Address

 

[Phantasm]

Your code does not fail for me
Does sheet "Shipped Orders" exist? Is the spelling correct? (think it would give a different error in any case)
Test this above the line that fails

MsgBox .AutoFilter.Range.Address

$A:$O

Is what I get. But, you know what, I formatted the sheet as a table...could that have anything to do with it?

 

[shg]

How about ...

  .Range("A1").CurrentRegion.AutoFilter 15, "x"

 

[Yongle]

It may well be related to the table - I suspect that was a recent change!

How can VBA offset a row when the range already includes EVERY rows in the worksheet? That is why it is failing

.AutoFilter.Range.Offset(1)

Try using this as your range - without any offset - it comprises data only

ActiveSheet.ListObjects(1).DataBodyRange

Try recording a macro when you filter the table

Read this useful guide
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

 

[DanteAmor]

If you have the whole sheet as a table, and the filter range is A: O, then when you increase the rank in a row offset(1), it exceeds the number of rows, so it sends the error.
It can be solved in this way.

Sub SHIPJOB(control As IRibbonControl)
    With Sheets("JOBS IN PROCESS NEW")
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("A:O").AutoFilter 15, "x"
        .AutoFilter.Range.Offset(1)[COLOR=#0000ff].Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).[COLOR=#0000ff]Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].EntireRow.Delete
        .AutoFilterMode = False
        If Not Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter Then
            Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter
        End If
        ActiveWorkbook.Sheets(3).Activate
        Rows("2:150").RowHeight = 16.5
    End With
End Sub

 

[Yongle]

@DanteAmor

I cannot test but should the resize not come before the offset?

this

.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)

or this??

.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)

 

[DanteAmor]

@DanteAmor

I cannot test but should the resize not come before the offset?

this

.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)

or this??

.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)

Do not worry, I can test it for you.
This form is correct:

.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).Copy

This form is wrong:

.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)

 

[DanteAmor]

If you have the whole sheet as a table, and the filter range is A: O, then when you increase the rank in a row offset(1), it exceeds the number of rows, so it sends the error.

A small detail, return this "A:O" to this "A1:O1" on the next line:

.Range("A1:O1").AutoFilter 15, "x"

 

[Yongle]

deleted by Yongle