Automatic Archive when spreadsheet is opened

ada111

Board Regular
Joined
Feb 19, 2015
Messages
74
I am trying to do an automatic archive macro where when the spreadsheet is opened, rows of data that are over three months old, automatically moves to an archive sheet that I created already. Is there a way to do this?? The cells on the archive sheet start with B10 go from B10-B..... wherever.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi ada111,

Could you give a better representation of your data. Such as Sheet names, cells that the date is found, number of columns that need to be moved, etc...

igold
 
Upvote 0
Hey sorry, I definitely rushed through that post.

The name of my sheets are "Active" (where the current data is and is updated daily by our client) and then the second sheet is "Archive". Columns B:J are filled out by our client. The date that the automatic archive would follow is in Column J, the "Patient Notification Date". It is when a patient is notified. And I have a re-sort button where the client clicks it and the blank cells go first and then it goes from newest to oldest date.

What I need is an automatic archive where when the spreadsheet is opened, dates that are older than 3 months are moved to the "Archive" sheet. It would move the entire row so an entire row from B:J would be moved if it is older than 3 months. And in the "Archive" sheet, the data needs to be placed starting at B10 and going from there.
 
Upvote 0
Is there data or a formula or something in B9 on the Archive sheet?
 
Upvote 0
Hi ada111,

This code will work, but for the first run there must be something in Cell B9. Once it runs once, it continue down the column with every subsequent run. This code must be placed in the "ThisWorkbook" object in the Visual Basic Editor. Please test this code on a back-up copy of your data. This code will write and delete data that is unrecoverable.

Code:
Private Sub Workbook_Open()

    Dim wAct As Worksheet: Set wAct = Worksheets("Active")
    Dim wArc As Worksheet: Set wArc = Worksheets("Archive")
    Dim dt As Date, arcdt As Date
    Dim lactRow As Long, iCtr As Long
    
    dt = Date
    arcdt = dt - 90
    lactRow = wAct.Cells(Rows.Count, "J").End(xlUp).Row
    
    For iCtr = lactRow To 2 Step -1
        If wAct.Cells(iCtr, 10).Value < arcdt Then
            wAct.Cells(iCtr, 10).Offset(0, -8).Resize(1, 9).Cut wArc.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        End If
    Next
    
End Sub

HTH

igold
 
Upvote 0
With you current workbook go to Save As and save it as another name, then you will have a copy to play with...
 
Upvote 0
Hey igold, It said that it was a run-time error '9': Subscript out of range. And then highlighted the SetwArc = Worksheets("Archive") line in yellow.
 
Upvote 0
I have the titles for each data entry. So I have the title of the spreadsheet taking up B:J rows 2-6. And then rows 7-9 are the titles of each data entry.
 
Upvote 0
Double check the name of your worksheets against the names in the code... The names you gave in your example are "Active" and "Archive". If that is not what the sheets are named then you get the subscript error.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top