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

Phantasm

Board Regular
Joined
Nov 2, 2007
Messages
58
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
Code:
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
 
Upvote 0
How about
Code:
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top