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

nakiasl

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • Example Workbook.PNG
    Example Workbook.PNG
    60.7 KB · Views: 5

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Thank you so much that worked perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,575
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top