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

 

[Phantasm]

Still getting an error, but now its "Object variable or With block variable not set" on the bold line of code

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).Resize(.AutoFilter.Range.Rows.Count - 1).Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 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]

As I explained earlier in the thread your range is too large to offset. So resize BEFORE the offset

amend that line to this

.AutoFilter.Range.[COLOR=#ff0000]Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].[COLOR=#006400]Offset(1)[/COLOR].Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[DanteAmor]

In post #9 I put a small correction:


Change "A:O" by "A1:O1"

Sub SHIPJOB(control As IRibbonControl)
    With Sheets("JOBS IN PROCESS NEW")
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("[COLOR=#ff0000]A1:O1[/COLOR]").AutoFilter 15, "x"
        .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 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

 

[Phantasm]

As I explained earlier in the thread your range is too large to offset. So resize BEFORE the offset

amend that line to this

.AutoFilter.Range.[COLOR=#ff0000]Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].[COLOR=#006400]Offset(1)[/COLOR].Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)

Same error with this:


Sub SHIPJOB(control As IRibbonControl)
With Sheets("JOBS IN PROCESS NEW")
If .AutoFilterMode Then .AutoFilterMode = False
.Range("A1:O1").AutoFilter 15, "x"
.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1).Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 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

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or 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.