Hello Scencea,
Lets use the DateDif function to help out on this.
In this case, I've used Column I as a temporary helper column into which I've temporarily inserted the following formula:-
=IF(F6<TODAY(),"""",DATEDIF(TODAY(),F6,""D""))
This will give us the number of days between today and when the next maintenance is due.
Hence, added to my previous code, the modified code will look like this:-
Sub DetermineTasks()
Dim lr As Long
Application.ScreenUpdating = False
lr = Tabelle1.Range("F" & Rows.Count).End(xlUp).Row
Tabelle1.Range("I6:I" & lr) = "=IF(F6<TODAY(),"""",DATEDIF(TODAY(),F6,""D""))"
With Tabelle1.[A5].CurrentRegion
.AutoFilter 9, "<=14"
.Offset(1).EntireRow.Copy
Tabelle2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
'.Offset(1).EntireRow.Delete
.AutoFilter
End With
Tabelle2.Range("A2", Tabelle2.Range("I" & Tabelle2.Rows.Count).End(xlUp)).Sort Tabelle2.[I2], 1
Tabelle2.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
Tabelle1.Columns(9).ClearContents
Tabelle2.Select
End Sub
Hence, the code will determine which tasks are due now and within fourteen (14) days and then transfer the relevant rows of data to Tabelle2. In Tabelle2, I've added Column I to show the number of days remaining (within the fourteen days) until the next maintenance task is due.
Column I in Tabelle1 is then cleared.
Here is the link to the updated sample:-
http://ge.tt/7MsYbBm2
I hope this helps.
Cheerio,
vcoolio.
unfortunately I don't get it to work properly. I'm just really new to vba and there is a long way to go. Here is my current code (Propably you'll just shake your head when you see it :D)
So at the Advise Sub it checks if something needs to be done in the next 7 days and in the Advise 2 Sub it checks if something is overdue.
It would be awesome if I could just copy the whole row (column A-I) to a new worksheet.
Thanks for your help and patience.
Cheers
Scencea