New to VBA, and struggling to create a simple archiving code

[dha90]

Hello
Any help would be appreciated
I have a table that is sorted automatically by dates and item type, and I want to create a macros that will archive older entries. By that I mean cut the items from the table and paste them in a different sheet.
My general idea is something like this:

Private Sub Workbook_Open() <- I want this function to take action every time the file is opened
For i = 1 To LastRow
If DateDiff(d, Date, Cells(i, 5)) > 7 Then <- Any entries older than 7 days ago should be archived
Cells(Target.Row, Target.Column).EntireRow.Cut Destination:=Sheets("archive").Range(i & Rows.Count).End(xlUp).Offset(1)
Next i
End If
End Sub

I cannot figure out how to make it work properly for the life of me, as it only cuts the first few boxes and pastes them into a new sheet it creates.

[Keebellah]

Welcome to the forum.
I understand that it's difficult and all that but I honestly have no experience with working with Ms-Off Ver: a.
I'm sure you took the quick path and just filled something in but I think all here that try to help where we can would like to know with what version you're working and if it's Windows or MAC.
So you see, you want help so take the time to seriously edit your profile, no need for personal data like location if you don't want to but at least the correct version and OS of you system will help.

[dha90]

Hi, thanks for the heads up.
I updated the profile, it's MSO 2013 running on Windows.

[Roel Jongman]

Not bad for a beginner
Biggest problem is that the datedif function is wrongly formatted it is not a valid function this way and unneeded to what you need.

Please Login or Register  to view this content.

some comments to the changes.
errors:
- lastrow is not existing variable you will need to give it a value yourself
I added line LastRow = Sheets("Data").Range("E"& Rows.count).End(xlUp).Row
you will have to change the "Data" to the actual sheetname of the sheet with the data that needs archiving
- Range( i& rows.count) will not give you a valid range, rows count will be 1048756 i will be 1 to lastrow so also a rownumber so it will evaluate int Range(11048756) which is not valid.
so either use Range("A"& Rows.count) or Cells(Rows.count,1) as startcel to find the last row the archivesheet

- as a guideline: be precize in loops most likely you will have an headerrow that you dont want be copied, so start at row 2 for the loop to avoid errors with multiple headers in your archive
- as a guideline: dont use worksheetfunctions in VBA unless you are pasting them into a cell as a function as part of the VBA project. 99 out of 100 times there is a VBA alternative code available that is faster
- as a guideline: avoid cutting or copying entire rows (or columns) , that copies 16384 cells most of which are empty. Only copy the actual used data area there are several ways to do that I picked .resize for this project. you will need to change the 10 to the actual number of columns in your data.





___________
sidenote:
proper format of function is
DATEDIF(Cells(i, 5), Date, "d")

[dha90]

Not bad for a beginner

Please Login or Register  to view this content.

some comments to the changes.
errors:
- lastrow is not existing variable you will need to give it a value yourself
I added line LastRow = Sheets("Data").Range("E"& Rows.count).End(xlUp).Row
you will have to change the "Data" to the actual sheetname of the sheet with the data that needs archiving
- Range( i& rows.count) will not give you a valid range, rows count will be 1048756 i will be 1 to lastrow so also a rownumber so it will evaluate int Range(11048756) which is not valid.
so either use Range("A"& Rows.count) or Cells(Rows.count,1) as startcel to find the last row the archivesheet
[/I]

That helped TREMENDOUSLY thanks a lot. I'm still struggling with some other issues though -
1. Cutting data from a table means filters and formulas are removed from it, leaving empty gaps instead. <- I might be able to work this one out by changing Excel's default settings
2. Archive data will be overwritten every time the code runs. <- I assume I would have to change the code so that the copy destination starts at the first empty row

[Roel Jongman]

Ok, well without an example file it is always hard(er) to see all effects.

You wrote that the data was sorted automaticly by date and item type so I assumed there was also other code to handle that and that this would resolve the gaps created by moving this data.

The data overwriting I did not expect as in the destination part of the cut line it is looking for the last line and the .offset(1) should handle going to the first blank line
All I can think of is that you should not compare against column A for the last filled row

But to be sure what to change and how to solve I would need to use an example.

[dha90]

The data overwriting I did not expect as in the destination part of the cut line it is looking for the last line and the .offset(1) should handle going to the first blank line
All I can think of is that you should not compare against column A for the last filled row

You're right, it works well enough on that regard, my bad.
The code works perfectly as long as I use Copy instead of Cut, but obviously I would also want these rows removed from the original table as well.
Using Cut causes Excel to lose the selected cells' formatting. Since the table uses a drop down system, this would be a problem.
I need to create something that works as if using the commands: Ctrl-A, Ctrl-X, instead of regular Ctrl-X.

I was thinking using Copy, Paste, and then a code or a function to clear the copied cells, but maybe there's something simpler...