Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Files into an archive
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Files into an archive
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. Sher wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Files into an archive
Thanks JW will give it a try.
"JW" wrote: 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. Sher wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Archive Data with Excel files | Excel Discussion (Misc queries) | |||
Moving files which are linked | Excel Discussion (Misc queries) | |||
Moving xls files that have links?? | Links and Linking in Excel | |||
Maintaining links when renamining/moving files | Excel Discussion (Misc queries) | |||
Moving Files in Excel | Excel Discussion (Misc queries) |