Is there a way to keep the current total when resetting/deleting cells?
Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 42

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

  1. #11
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #12
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  3. #13
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #14
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    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:
    Code:
    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.
    Last edited by Trevor_S; Mar 27th, 2019 at 02:31 AM.

  5. #15
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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")

  6. #16
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  7. #17
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Last edited by Trevor_S; Mar 27th, 2019 at 07:02 PM.

  8. #18
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by Trevor_S; Mar 27th, 2019 at 07:06 PM.

  9. #19
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  10. #20
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •