Transferring Data Based on Date/Age to Another Sheet

Jay K · Mar 10th 2016

I am in need of assistance to identify the age of a row based on an entered date and once the entry is 365 days, to move it to another sheet within the workbook. I have several columns of data within each row and want to identify when it ages to 1 year old, and have it automatically moved from the main sheet to an archive sheet within the same workbook. If needed, there is data in columns A through M, the date for each row is in column C and entries run from row 2 through 51,000.

I cannot seem to find anything in VBA or a macro that matches exactly what I am trying to do and have attached the file that I am trying to set this up in.

Thank you for any guidance or assistance.

skywriter · Mar 10th 2016

Re: Transferring Data Based on Date/Age to Another Sheet

You can try this.

It needs to be installed in the worksheet module for the "Associate Tracker" worksheet.

The attached file has it installed.

Code

Sub MoveData()
Dim x, wsAD As Worksheet
x = Application.InputBox("Enter a date", Type:=1)
If x = False Then Exit Sub
Application.ScreenUpdating = False
Set wsAD = Worksheets("Archive Data")
    With Me.Cells(1).CurrentRegion
        .AutoFilter 3, ">=" & x + 365
            With .Offset(1).SpecialCells(12)
                .Copy wsAD.Cells(wsAD.Rows.Count, "A").End(xlUp).Offset(1)
                Application.EnableEvents = False
                .EntireRow.Delete
                Application.EnableEvents = True
            End With
        .AutoFilter
    End With
Application.Goto Me.Cells(1)
Application.ScreenUpdating = True
End Sub

Display More

Transferring Data Based on Date/Age to Another Sheet

Participate now!

Share