Automatically Archive Historic Or Old Data Based On Ageing Date

[peter renton]

Hi

I have attached a small sample sheet

I dont know if this is possible but i would like to remove a complete row and place it in the next available
row in a sheet called archive if the date in columd H is say 12 months after todays date and then if possible
delete it from the archive sheet if its say 18 months past todays date.

the list continues to grow and i was hoping for a way to self clean the data


Thaks for any help

[Excel Fox]

Private Sub Workbook_Open()

Dim lng As Long
Dim dtmDate As Date
With Worksheets("Contracts")
For lng = .Cells(.Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(.Cells(lng, "H").Value) Then
dtmDate = .Cells(lng, "H").Value
If DateSerial(Year(dtmDate), Month(dtmDate) + 12, Day(dtmDate)) <= Date Then
.Rows(lng).Cut Worksheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2)
.Rows(lng).Delete
End If
End If
Next lng
End With
With Worksheets("Archive")
For lng = .Cells(.Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(.Cells(lng, "H").Value) Then
dtmDate = .Cells(lng, "H").Value
If DateSerial(Year(dtmDate), Month(dtmDate) + 18, Day(dtmDate)) <= Date Then
.Rows(lng).Delete
End If
End If
Next lng
End With

End Sub

[peter renton]

Wow


Thank you very much this is spot on :-)))))

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3tnAjh ZU (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3tnAjh ZU)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3KswxL 3c (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3KswxL 3c)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=UgyBXFxnVWT3pqtdqPx4AaABAg (https://www.youtube.com/watch?v=suUqEo3QWus&lc=UgyBXFxnVWT3pqtdqPx4AaABAg)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY (https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p)
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547 (http://www.eileenslounge.com/viewtopic.php?p=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (http://www.eileenslounge.com/viewtopic.php?p=323516#p323516)
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517 (http://www.eileenslounge.com/viewtopic.php?p=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (http://www.eileenslounge.com/viewtopic.php?p=323226#p323226)
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150 (http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (http://www.eileenslounge.com/viewtopic.php?p=322955#p322955)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41659)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY (https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgxREWxgx2z2Lza_0st4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgxREWxgx2z2Lza_0st4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgyikSWvlxbWS24NBeR4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgyikSWvlxbWS24NBeR4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgwNiH4hhyrd2UjDK8d4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgwNiH4hhyrd2UjDK8d4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HAAf952WoU ti (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HAAf952WoU ti)
https://www.youtube.com/watch?v=hz4vb48wzMM&lc=Ugy2N3gvXBNrvWpojqR4AaABAg (https://www.youtube.com/watch?v=hz4vb48wzMM&lc=Ugy2N3gvXBNrvWpojqR4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (http://www.eileenslounge.com/viewtopic.php?p=322462#p322462)
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356 (http://www.eileenslounge.com/viewtopic.php?p=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (http://www.eileenslounge.com/viewtopic.php?p=322150#p322150)
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111 (http://www.eileenslounge.com/viewtopic.php?p=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (http://www.eileenslounge.com/viewtopic.php?p=322086#p322086)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[peter renton]

How do i add the used contracts page so it will auto move the data from that sheet as well? i have tried to add the name in the code
but comes up with an error?

Peter