Need help with a macro to sort a range of data based on due dates when pressing a button

**Atrains** · 09-14-2014, 01:37 PM

I have recorded a macro and it seemed to work fine except if I add a new row of data to the sheet, the macro stops working correctly or ignores some of the rows. Each assembler has their own sort button which sits above the due date on the sheet. The sort buttons are linked to the macro. How can I create a macro that sorts each assembler's range based on the due dates so the oldest dates are at the top and newest jobs (further out) are at the bottom of their list? What happens if I insert a new row?

The spreadsheet is a simple job schedule workbook in Excel 2003 that the assemblers like for its simplicity. I am trying to streamline it and make it more functional for them. One thing I tried to do was create a sort macro for due dates. I only want to sort the range for each assembler. There are assemblers 5 listed. I don't want to sort the entire sheet at once, just their scheduled work.

Thanks,

Atrains

Worksheet is below as an example.

REMEMBER TO CHECK "NEW JOBS TO BE SCHEDULED" WORKSHEET
D
Customer # B W/O QTY Size Figure No DUE Notes Notes Status Hrs
Stock 1 75197 20 2" 4000S(57)-2.00 8/28/2014 Valves Built Need Testing 12
Stock 2 75273 15 2" 4001S(57)-2.00 9/9/2014 Must Pull Parts Bonnets in Lapping 10
Stock 3 75315 30 2" 4001A-2.00 9/26/2014 Must Pull Parts 10
Stock 5 75306 10 2" B6571-2.00 10/1/2014 Must Pull Parts 8
SB 7 75313 2 3" SP1661S(57) 10/14/2014 Reviewing JOB - BON-D/R AT NACE H/T HANDWHEEL VALVE PRIMER3 PAINT ONLY 6
AG 75452 1 12" BCO2SVGO 10/24/2014 Reviewing JOB Bonnets in lapping Norgren sol and go's 8
Stock 9 75398 5 2.5" B6561-2.50 10/24/2014 Need to pick parts 6
Stock 75470 30 2" 4061S(57)-2.00 12/9/2014 Need IBH3100 - In welding Stk replacements Need Inlets and outlets 20
LAPPING / TESTING x 0
Assembly Hrs 80

S
Customer # B W/O QTY Size Figure No DUE Notes Notes Status Hrs
Stock 1 75286 20 2" 4060S(57)-2.00 9/25/2014 valve built need testing/hand wheel 10
VC 2 75290 4 6" BA02SP 5/20/2001 Need A/C's Due: 9/15 10
Stock 3 75300 10 6" BMVA-6.00 7/7/2014 Must Pull Parts Bonnets in Lapping 30
Stock 4 75341 15 2" 4001S(57)-2.00 9/30/2014 Must Pull Parts no bonnets /grinding 15
CP 6 75336 1 2" 4061S(58)-2.00 10/1/2014 Must Pull Parts Outlet Due: 9/19 IN Welding 4
SB 5 75401 2 2" SPBMV-2.00 10/1/2014 Valves Built & Tested Missing H023T due ??? 6
Stock 7 x 75206 20 1.5" 4001s(57)-1.50 ? valves built testing 10
ES 8 75381 1 8 Gal ARG008EXUE3 10/7/2014 Need press switch,gauge, and reg. Due 9/24 Tanks in assy. 4
SS 9 75368 1 3" SPBMV-3.0 10/24/2014 Reviewing JOB (HOT VALVE) NO A/C ,ACT SHAFT 3
Assembly Hrs 92

M
Customer # B W/O QTY Size Figure No DUE Notes Notes Status Hrs
A3IM 2 75440 1 2" SPBMV-2.00 10/6/2014 READY D.E. A/C + NO ACC Bonnets lapped 4
DC 8 75399 1 4" SP1671S(56) 10/14/2014 READY Bonnets IN LAPPING 4
ES 6 75338 2 3" SPPRO-3" 10/17/2014 Need P&L's arm from H/T P/L NACE 4
P66 7 74841 1 4" LD301S1NASR 10/9/2014 valve built Bettis due 10/24 12
Stock 1 75456 5 1" 4061S(57)-1.00 10/6/2014 Must Pull Parts/bonnets in lapping Stk replacement only 1 left 12
Stock 3 75360 10 2" BMVA-2.00 10/8/2014 Must Pull Parts bonnets in lapping 15
TM 74088 3 4" SP1571YH(57) 9/15/2014 valves built waiting for Bettis 12
VC 4 75455 5 2" 4060S(58)-2.00 10/29/2014 Must Pull Parts/bonnets in lapping 1 in stk,need 2 valves Balance for stock 12
VC 5 75454 5 1" 4060S(58)-1.00 10/29/2014 Must Pull Parts/bonnets in lapping ZERO IN STOCK in process 12
Assembly Hrs 97
R
Customer # B W/O QTY Size Figure No DUE Notes Notes Status Hrs
Stock 1 75284 100 1-1/4" 4000A-1.25 9/5/2014 Must Pull Parts 20
Stock 4 75437 10 2" 4260A-2.00 10/30/2014 Must Pull Parts NEW 15
Stock 5 75453 100 2" 4000A-2.00 11/5/2014 Must Pull Parts 30
Stock 2 75299 100 2" 4000A-2.00 9/26/2014 60 of 100 built in process 20
Stock 3 75367 100 1-1/2" 4000A-1.50 10/9/2014 Must Pull Parts 20
Assembly Hrs 105
Floater
Customer # B W/O QTY Size Figure No DUE Notes Notes Status Hrs

JOBS ON HOLD - WAITING FOR MORE INFO
LC hold 69195 1 6" SP1671CMCC(57) 8/11/2012 Not Acc's Valve Built Steps 50 thru 80 NOT Released
LC hold 69196 1 2" SP1671SCC(57) 8/11/2012 Not Acc's Valve Built Steps 50 thru 80 NOT Released
LC hold 69193 1 3" SP1671CMCC(57) 8/11/2012 Not Acc's Valve Built Steps 50 thru 80 NOT Released
LC hold 69197 1 2" SP1671CMCC(57) 8/11/2012 Not Acc's Valve Built Steps 50 thru 80 NOT Released
LC hold 33 GAL ARG033EXUE7 8/11/2012 NOT RELEASED YET
LC hold 70112 2 8 GAL ARG008EXUE7 8/11/2012 Air Reservoirs - Need CM Paint HOLD
LC hold 70732 1 200GAL ARG200EXUE7 8/11/2012 Need Pressure Switch - Not on Order CM PAINT REQ.

**Leith Ross** · 09-14-2014, 02:10 PM

Hello Atrains,

Welcome to the Forum!

You will get answers faster and that are more accurate by posting your workbook.

Please redact any sensitive or confidential information before you upload the workbook.

How To Post Your Workbook

At the bottom right of the Reply window, Click the button Go Advanced
At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
Click the Select Files button at the bottom middle of the dialog.
In the new window Find the file you want to upload, select it, and Click Open.
You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
Wait until the file has completely uploaded before you exit the File Manager dialog.