Moving a row of data to another sheet after a period of time

[nakiasl]

I have an attendance workbook im creating. i need to be able to us a button I have created to move data from a row after a year has past to another sheet within the work book that will hold the archived attendance records. for example employee a was tardy on 02/12/21 and received a point. On 02/12/22 that point will roll off and that row needs to be moved from the current attendance sheet to the archived sheet for that employee. can you help with this

 

[Yongle]

welcome to the forum

VBA below assumes that the DATE being tested is in column A in sheet named "Current"
If that DATE is earlier or equal to ONE YEAR AGO then the row is moved to sheet "Archived"

amend to suit the layout in your workbook

Sub MoveOldData()
    Dim wsA As Worksheet, wsC As Worksheet, r As Long, lr As Long, cel As Range, YearAgo As Date
    YearAgo = DateSerial(Year(Date) - 1, Month(Date), Day(Date)) + 1    'adding 1 reduces <= test to <
    Set wsA = Sheets("Archived")
    Set wsC = Sheets("Current")
    Set cel = wsA.Cells(wsA.Cells.Rows.Count, 1)
    lr = wsC.Cells(wsC.Rows.Count, 1).End(xlUp).Row                     'determine last row with value in column A
    For r = lr To 2 Step -1                                             'start at bottom when deleting rows
        If wsC.Cells(r, 1) < YearAgo Then                               'test date value in column A
            With wsC.Cells(r, 1).EntireRow
                .Copy Destination:=cel.End(xlUp).Offset(1)              'copy data to next row in sheet "Archived"
                .Delete                                                 'delete original row
            End With
        End If
    Next r
End Sub

 

[nakiasl]

Thank you so much for replying to me. This works, but not quite the way I need it to. When I create my button and run the macro it moves the cells up and moves the date that needs to be archived and the button to the archived sheet. I want to be able to move the attendance data that rolls off to the archived sheet but I don't want to change or shift any of the other cells on the current sheet. I hope that makes since. I uploaded a screen shot of the workbook. I need to keep everything in columns A through H and rows 1 through 5 in its same location with all its same formatting and formulas. Your help will be greatly appreciated.

Nakia

 

[Yongle]

To only deal with rows below row 5 ....
For r = lr To 6 Step -1

I need to keep everything in columns A through H in its same location with all its same formatting and formulas

"Before" and "desired" view of current sheet would be useful

 

[nakiasl]

Thank you so much that worked perfectly