Moving Files into an archive

[Guest]

I have a project list in excel that I track and would like to know if there
is a way when an item is completed I can set it up that it automatically goes
to an archive worksheet the following week.

 

[JW]

Yes, that would be possible using VBA. You would need to have a
completion date in your data though. Then you could usse the
Workbook_Open event (or Auto_Open) to cycle through the main sheet and
anything that has a completion date les than todas date minus 7 days.
Something like:

One Way:
Private Sub Workbook_Open()
Dim r As Long
With Sheets("Main")
For r = 2 To .Cells(65536, 1).End(xlUp).Row
With .Cells(r, 1)
If .Value < Date - 7 Then
.EntireRow.Cut Destination:= _
Sheets("Archive").Cells(65536, 1) _
.End(xlUp).Offset(1, 0)
End If
End With
Next r
End With
End Sub

This code would need to be placed in the ThisWorkbook code module.
The above code doesn't handle the deletion of the cut row. That could
always be incorporated though. Also, this is assuming that the
completion date is in column A.

 

[Guest]

Thanks JW will give it a try.

Yes, that would be possible using VBA. You would need to have a
completion date in your data though. Then you could usse the
Workbook_Open event (or Auto_Open) to cycle through the main sheet and
anything that has a completion date les than todas date minus 7 days.
Something like:

One Way:
Private Sub Workbook_Open()
Dim r As Long
With Sheets("Main")
For r = 2 To .Cells(65536, 1).End(xlUp).Row
With .Cells(r, 1)
If .Value < Date - 7 Then
.EntireRow.Cut Destination:= _
Sheets("Archive").Cells(65536, 1) _
.End(xlUp).Offset(1, 0)
End If
End With
Next r
End With
End Sub

This code would need to be placed in the ThisWorkbook code module.
The above code doesn't handle the deletion of the cut row. That could
always be incorporated though. Also, this is assuming that the
completion date is in column A.