Page 1 of 5 123 ... LastLast
Results 1 to 10 of 50

Thread: Automate date range entry

  1. #1
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Automate date range entry

    I'm the treasurer of my HOA. I created a budget spreadsheet that works well but I will be giving up the position a some point so I want to create a Dashboard for the person who takes over for me, to make it "user friendly".

    Every year I have to start with a new sheet, changing the dates, removing data, etc. Since the fiscal year starts next month I decided to work on automating the process of setting up the new spread sheet.

    see the attached link for details.


    https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    803
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Assuming you want columns A & B left intact
    maybe a little macro like this
    Code:
    Sub ChangeDates_ClearData()
    
    Dim cel As Range
    
        'Dates
        For Each cel In Range("C1:N1")
            cel.Value = DateAdd("yyyy", 1, cel.Value)
        Next cel
    
        'Clear data
        Range("C2", Range("C2").SpecialCells(xlLastCell)).ClearContents
    
    End Sub
    Last edited by NoSparks; Jun 20th, 2019 at 07:22 PM.

  3. #3
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Assuming you want columns A & B left intact
    maybe a little macro like this
    Code:
    Sub ChangeDates_ClearData()
    
    Dim cel As Range
    
        'Dates
        For Each cel In Range("C1:N1")
            cel.Value = DateAdd("yyyy", 1, cel.Value)
        Next cel
    
        'Clear data
        Range("C2", Range("C2").SpecialCells(xlLastCell)).ClearContents
    
    End Sub
    Looks good, thanks.

    I'll run it and see what happens. Plus I'll study it to see what I can learn from it, and how I can apply the methods/parameters (I still struggle with sytam and nomenclature but I taking an on line course to address that) in other situations.

  4. #4
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Assuming you want columns A & B left intact
    maybe a little macro like this
    Code:
    Sub ChangeDates_ClearData()
    
    Dim cel As Range
    
        'Dates
        For Each cel In Range("C1:N1")
            cel.Value = DateAdd("yyyy", 1, cel.Value)
        Next cel
    
        'Clear data
        Range("C2", Range("C2").SpecialCells(xlLastCell)).ClearContents
    
    End Sub
    A really odd thing happened when I ran this macro. For each pass it took out data in respective the columns from rows 22-32.

    Since there isn't any personal information in the workbook I'll send it to you to look at. Maybe I did something wrong when I installed that macro, not sure.

    While your at it could you add a line that makes the Master sheet active, so there's no change the macro will try to modify another sheet by mistake?

    https://app.box.com/s/13e36ujuhy83kx52fufn1gk4qi47hjqk
    Last edited by mikecox39; Jun 20th, 2019 at 11:25 PM.

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    803
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    I think this will do what you're after with a couple of exceptions,
    cells that have been manually colored,
    and current balance N44 and T19 which appear to be manual entries.
    Code:
    Sub ChangeDates_ClearData()
    
    Dim cel As Range
    
    Sheets("Master").Select
    
    With Sheets("Master")
        'Dates
        For Each cel In .Range("C1:N1")
            cel.Value = DateAdd("yyyy", 1, cel.Value)
        Next cel
    
        'Clear data
        .Range("C3:N11").ClearContents
        .Range("C13:N14").ClearContents
        .Range("C35:N40").ClearContents
        .Range("C3:N40").ClearComments
        
    End With
    
    End Sub
    For each pass it took out data in respective the columns from rows 22-32.
    The formulas in those rows don't find anything in tbl_Details for months that are in the future and therefore the cells are blank.

    Hope that helps.

  6. #6
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    I think this will do what you're after with a couple of exceptions,
    cells that have been manually colored,
    and current balance N44 and T19 which appear to be manual entries.
    Code:
    Sub ChangeDates_ClearData()
    
    Dim cel As Range
    
    Sheets("Master").Select
    
    With Sheets("Master")
        'Dates
        For Each cel In .Range("C1:N1")
            cel.Value = DateAdd("yyyy", 1, cel.Value)
        Next cel
    
        'Clear data
        .Range("C3:N11").ClearContents
        .Range("C13:N14").ClearContents
        .Range("C35:N40").ClearContents
        .Range("C3:N40").ClearComments
        
    End With
    
    End Sub

    The formulas in those rows don't find anything in tbl_Details for months that are in the future and therefore the cells are blank.

    Hope that helps.
    Thanks for the code for selecting the master sheet. I was able to use that example to add a new line to the macro that selects and deletes the data in the Details sheet.


    There is a Budget sheet in the workbook. I want to pull the totals from the previous worksheet into it. Can you help me the code for importing data from the cells of another workbook's sheet?


    Again thanks! The macro worked perfectly! I will assign it to the first Button on my Budget Dashboard!


    When I have finished I will be free to resign as Treasurer knowing the next Treasurer will not need a lot of hands on training on how to use my spreadsheets and will be able to maintain an accurate accounting of income and expenses as I have for the past 8 years.

  7. #7
    Board Regular
    Join Date
    Mar 2013
    Posts
    803
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Glad to have helped.

    Regarding this
    There is a Budget sheet in the workbook. I want to pull the totals from the previous worksheet into it.
    it would depend on what your 'new' Budget sheet is to be like. If you share a mock-up I'll have a look.

  8. #8
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Glad to have helped.

    Regarding this it would depend on what your 'new' Budget sheet is to be like. If you share a mock-up I'll have a look.
    Is this what you need?

    https://app.box.com/s/tng7y5aybn345b2idvu80e8zhozatclb

    Can a line of code be created that would generate the filename of last years workbook, based on this year's workbook, MB19 in this case, then subtracting 1 from the integer, so you would get MB18; last years workbook. That way the user wouldn't have to provide the name of last years workbook, and possibly get it wrong, it would be generated using the integer in the filename of the current workbook.

    Once the previous year's workbook was identified the data from it's Master sheet could be pulled into the Budget sheet using whatever method does that.

    Is that doable or is there be a better way?
    Last edited by mikecox39; Jun 22nd, 2019 at 12:50 AM.

  9. #9
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Is there a way to save the newly cleared worksheet using a formula that used the string MB in the filename and added integer. Something like save as "MB & [nn from the current year]? That way no one could change the name format and no error could occur from failing to follow the naming format. How would you actually write that so the appropriate filename would get generated?

  10. #10
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Here's the updated workbook with the Budget sheet


    https://app.box.com/s/13e36ujuhy83kx52fufn1gk4qi47hjqk

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
  •