Highlight Jobs that are due. Closed

Question

Not even sure where to begin on this one and not sure it's possible either but thought it's worth a try. I have a document to record date's when jobs need to be completed by and at the moment all the information is listed in a normal excel spreadsheet. Is there a way that I can create a seperate tab at the front which will automatically pick up all the jobs that are either overdue or nearing due date just so I dont have to keep filtering and looking for what jobs are not greened out. Not even sure that makes sense, I want a front page that registers when a job's due date is upcoming and transfers a copy of the information to a seperate tab that will only show all the one's that are either overdue or coming up to their due date

Thanks

vcoolio · Answer

Hello MJones,

I'm not exactly sure how you want to go about this, but the following code should be a start:-


Private Sub Workbook_Open()

Application.ScreenUpdating = False

Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
    Dim lRow As Long
    Set ws = Worksheets("Schedule")
    Set ws1 = Worksheets("About Due")
    Set ws2 = Worksheets("Overdue")
  
lRow = Range("A" & Rows.Count).End(xlUp).Row
 
ws.Select

For Each cell In Range("M2:M" & lRow)
    If cell >= [Today()] Then
    Range(Cells(cell.Row, "A"), Cells(cell.Row, "M")).Copy
    ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    'Range(Cells(cell.Row, "A"), Cells(cell.Row, "M")).ClearContents
    End If
Next

For Each cell In Range("M2:M" & lRow)
    If cell < [Today()] Then
    Range(Cells(cell.Row, "A"), Cells(cell.Row, "M")).Copy
    ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    'Range(Cells(cell.Row, "A"), Cells(cell.Row, "M")).ClearContents
    End If
Next

'Columns("A").SpecialCells(4).EntireRow.Delete
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


The code actually separates the jobs that are coming up for action and those that are overdue for action into two separate work sheets (About Due and Overdue). The first sheet (Schedule) is where you input the job details.

I have attached a test sample work book for you to peruse here:-

https://www.dropbox.com/s/kct2xn0hbbhl3u6/MJones447.xlsm?dl=0

The code is a WorkBook_Open event which means that as soon as you open the work book, the code will do its thing and transfer the relevant details to the relevant work sheet. You can then continue on with new entries in the "Schedule" sheet. So, on opening the work book on a daily basis, the data will be checked (by date) and transferred to the relevant sheet as required.

In the sample work book, you will see the "Schedule" sheet has the job details and the "About Due" and "Overdue" sheets will have their relevant details. You won't actually see the data transfer happen. 

Ideally, you would want the "used" data in the "Schedule" sheet to be cleared other wise you will end up with duplicates in the other two sheets and the "Schedule" sheet will become cluttered with un-needed data. You will see in the above code that I have made allowance for the "used" data to be cleared but have, in the meantime, commented it out (the green lines of code). To have this part of the code work, just remove the apostrophe from the front of the line of code.

To implement the code into your own work book, click on the Developer tab, over on the far left select Visual Basic. This will open the Visual Basic Editor. Over on the left side, double click on "ThisWorkbook" and then paste the above code into the big white field.

I hope that this helps.

Cheerio,
vcoolio.

Highlight Jobs that are due.

1 response

Similar discussions

Subscribe To Our Newsletter!