Is there a way to keep the current total when resetting/deleting cells?

[PlumGr8]

So i have a formula for counting even that have occurred at certain sites. Like different job sites doing different events and a simple one that basically says

Job site 1 did this
Job site 2 did that

we delete everything older then 2 months as the year progresses, just to keep file sizes smaller. Is there a way to create a formula that will maintain the overall total though? So, that say there was 450 events, and we delete 2 months worth, that the total wont decrease and will always maintain and increase its value? I don't think there is, but this site is awesome for help so couldn't hurt to ask.

 

[PlumGr8]

Holy crap and awesome haha. I should have time today, i am going to try and see how it goes. I will report back for sure, thanks again. Looks like a pain haha and wayyyyy over my head

 

[PlumGr8]

Ok, i got it to work. I did move a couple things since then, but i got that all figured out so no issues, but another question. Do i have to manually run this macro? Or is there a way to have it so it just always runs? So, say i have it form 1/1/19 until 3/26/19, and i have 350 total events in that span currently. Now say i want to auto limit it to 45 days. So it will delete jan and half of feb and add it to my overall total so as i add events from today and tomorrow etc it will keep counting accurately. But is there a way to just have it always running, so people that follow on after me won't have to run it and just be all automated?

 

[PlumGr8]

Sorry, multiple replies. Kind of ignore most of my previous one. All works, i did find the VBA code to auto run "i think", but how do i modify it to just auto run at a set date value. 60 days, 75 days etc. Obviously i can probably edit that in the macro if i need to, just not sure how to alter it so it just auto runs. And i really owe you a few drinks or a good glass of scotch or something haha.

 

[Trevor_S]

Auto running: change this line:
Sub DeleteOldRecords()
To:
Private Sub Workbook_Open()

Also make sure that its in the This Workbook section of the VBAEditor.

I'd also suggest adding the sheet name in the macro, as if it auto runs, you can't be sure that the correct sheet is active first. So everywhere that you see the word Range in the macro, put Sheets("sheet name"). in front of it.

If you decide to protect the workbook, you'll need to let the macro know the password so that it can delete the rows. If the password is ABC, you'll need to put the first line below before the Do line, and the second after the Loop line:
Active.Workbook.Unprotect("ABC")
Active.Workbook.Protect("ABC")

If you've protected at sheet level, change Workbook to Worksheet.

To fix the number of days, replace:

DaysOld = InputBox("Delete entries more than this many days old", "Delete old records")
If IsNumeric(DaysOld) = False Or DaysOld = False Then
    'Either non-numeric / blank entered, or user cancelled
    MsgReply = MsgBox("No records deleted.", vbOKOnly, "Delete old records")
    End
End If

with:

DaysOld = 60

or however many days you want. You can also stop the notification at the end by removing the line containing MsgBox at the end of the macro.

 

[Trevor_S]

A correction to the above - if you've protected the workbook, the macro lines should be:
ActiveWorkbook.Unprotect("ABC")
ActiveWorkbook.Protect("ABC")

Note no dot between Active and Workbook. If its just the sheet that is protected, you'll need to also specify the sheet name, rather than just assume its the active one, i.e.:
Sheets("sheet name").Unprotect("ABC")
Sheets("sheet name").Protect("ABC")

 

[PlumGr8]

Ok, so this is where i am at. I am pretty sure i m just doing something dumb but this is what i got. So under the 'this workbook" which is at the bottom of all the sheets i enter am entering this

Private Sub Workbook_Open()
DaysOld = 60
CheckRow = 3 'This is the first row to check - it assumes that row 1 is a header
RecordsFound = 0 'This is a counter of the number of rows deleted

Do 'Go through records, if the record is old enough to delete then before deleting, check for job sites in cols G and H and update the value in col N
'Stop when column A is blank
If Sheet38(“Job Data”).Range("A" & CheckRow).Value = "" Then Exit Do
If Sheet38(“Job Data”)Range("A" & CheckRow).Value < (Now - DaysOld) Then
'This record needs to be deleted - check if it contains any Job Sites and update the totals
If Sheet38(“Job Data”)Range("G" & CheckRow).Value = "Job Site 1" Then Sheet38(“Job Data”)Range("N10").Value = Range("N10").Value + 1
If Sheet38(“Job Data”)Range("H" & CheckRow).Value = "Job Site 1" Then Sheet38(“Job Data”)Range("N10").Value = Range("N10").Value + 1
If Sheet38(“Job Data”)Range("G" & CheckRow).Value = "Job Site 2" Then Sheet38(“Job Data”)Range("N11").Value = Range("N11").Value + 1
If Sheet38(“Job Data”)Range("H" & CheckRow).Value = "Job Site 2" Then Sheet38(“Job Data”)Range("N11").Value = Range("N11").Value + 1
If Sheet38(“Job Data”)Range("G" & CheckRow).Value = "Job Site 3" Then Sheet38(“Job Data”)Range("N12").Value = Range("N12").Value + 1
If Sheet38(“Job Data”)Range("H" & CheckRow).Value = "Job Site 3" Then Sheet38(“Job Data”)Range("N12").Value = Range("N12").Value + 1
'Then delete the record
Sheet38(“Job Data”)Range("A" & CheckRow & ":K" & CheckRow).Delete (xlShiftUp)
RecordsFound = RecordsFound + 1
Else
CheckRow = CheckRow + 1
End If
Loop

End Sub




When i try to run, it bring me up a window to select a macro, there are a few i did last yr, that i am working on replacing, but i type in the new name i want and create etc, then it enters all this under the modules section? But i did get it to a point where it ran on startup but with a debug error in yellow referencing here
If Sheet38(“Job Data”).Range("A" & CheckRow).Value =

So, pretty sure i am just doing something silly. The workbook isn't protected, neither is the sheet actually which i am trying to work on. I plan to lock these cells and another one and protect the sheet, probably with with a password as people will have to change things at times, but i plan to protect it at least for these cells and another column while leaving the others unprotected for data entry. But we are almost there at least haha.

 

[Trevor_S]

If its in the This Workbook section of that spreadsheet in the VBA editor, you shouldn't need to try to run the macro. As soon as you try to open the spreadsheet, it should run the macro prior to displaying the spreadsheet on screen?

 

[Trevor_S]

Just noticed that you're putting Sheet38 in the macro. It should be Sheets as in my macro, you just need the sheet name between the quotes, e.g.:
Sheets(“Job Data”).Range("A" & CheckRow).Value

 

[PlumGr8]

OK, so in the VB for Application, under Microsoft Excel Objects, it list all the sheets and at the end says "ThisWorkbook". That is where i have the entry written. Starting the file it gives me the Runtime error and to end or debug. Debug i get the yellow caution on - If Sheet38("Job Data").Range("A" & CheckRow).Value = " " Then

I also have a lot of sheet that open to debug that don't even have anything in them, need to get those taken care of also haha.

 

[PlumGr8]

Ok got it, i also had a mistype i found as well. So awesome. I actually have to do something like this for another set of cells. Not the auto delete part, but i have a set of cells with values that i also need to save even after cells are deleted. I think i can use part of your macro above. I was gonna try to figure it out before bugging you again haha. But if you no it easily, say i have a formula using sumprodcuts etc that give me totals in cells Z3-Z10. Where should i enter a macro to also keep those totals after the source columns are deleted. Pretty much just like the Job Site, this one is basically work completed.