Highlight Jobs that are due.
Closed
MJones447
Posts
2
Registration date
Friday June 5, 2015
Status
Member
Last seen
June 5, 2015
-
Jun 5, 2015 at 09:36 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jun 7, 2015 at 09:50 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jun 7, 2015 at 09:50 PM
Related:
- Job aborted failure in uio createaddressfromipaddress
- Job aborted: failure in uio createaddressfromipaddress - Guide
- Job aborted failure in uio create address from ip address ✓ - Printers & Scanners Forum
- Job aborted certificate fetching failure ✓ - Hardware Forum
- Unable to determine volume version and state. chkdsk aborted - Hard Drive & SSD Forum
- HP Printer missing files called serscan ✓ - Windows XP Forum
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Jun 7, 2015 at 09:50 PM
Jun 7, 2015 at 09:50 PM
Hello MJones,
I'm not exactly sure how you want to go about this, but the following code should be a start:-
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.
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.
