Looking for a way to do multiple operations during an AutoFilter function in VBA

[Phantasm]

I am trying to hone a project management sheet that I am working on. Right now, I have a macro that sorts & moves rows of data to another sheet based upon a query of a columns contents...What this is for is when a job is complete, the user enters the shipper number in its "SHIP JOB" column & runs the macro, the sheet will filter the active jobs on the sheet based upon if the value of the "SHIP JOB" cell is not empty. Any non empty cells have that row copied, pasted onto another sheet (SHIPPED JOBS) & deleted from the active jobs sheet. This is the macro I use for this:

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


What I need to do now is come up with a way to do partial shipments. So right now column C is my order quantity. Column O would be the partial quantity that I want to ship. What I need to do is the following:

- Filter my sheet to show any rows that have a non empty cell in column O
- Copy and paste these cells from the active jobs sheet to the shipped jobs sheet
- On the active jobs sheet subtract each shown rows column O number from the column Cs number, leaving the quantity remaining to ship in column C
- After that calculation is done, remove the value in column O
- Unfilter the active job sheet to show all jobs again.

I hope I am explaining this clear enough. Thanks in advance for the help!

 

[Fluff]

How about

Sub Phantasm()
   With Sheets("JOBS IN PROCESS NEW")
      If .AutoFilterMode Then .AutoFilterMode = False
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace(Replace("if(@o<>"""",@-@o,@)", "@o", .Offset(, 12).Address), "@", .Address))
      End With
      .Range("A1:O1").AutoFilter 15, "<>"
      .AutoFilter.Range.Offset(1).Copy Worksheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilter.Range.Columns(15).Offset(1).Value = ""
      .ShowAllData
   End With
End Sub

 

[Phantasm]

How about

Sub Phantasm()
   With Sheets("JOBS IN PROCESS NEW")
      If .AutoFilterMode Then .AutoFilterMode = False
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace(Replace("if(@o<>"""",@-@o,@)", "@o", .Offset(, 12).Address), "@", .Address))
      End With
      .Range("A1:O1").AutoFilter 15, "<>"
      .AutoFilter.Range.Offset(1).Copy Worksheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilter.Range.Columns(15).Offset(1).Value = ""
      .ShowAllData
   End With
End Sub

It worked like a charm. Thank you for the help with this.

 

[Fluff]

You're welcome & thanks for the feedback