Automate date range entry

[mikecox39]

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

 

[NoSparks]

Assuming you want columns A & B left intact
maybe a little macro like this

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

 

[mikecox39]

Assuming you want columns A & B left intact
maybe a little macro like this

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.

 

[mikecox39]

Assuming you want columns A & B left intact
maybe a little macro like this

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

 

[NoSparks]

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.

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.

 

[mikecox39]

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.

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.

 

[NoSparks]

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.

 

[mikecox39]

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?

 

[mikecox39]

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?

 

[mikecox39]

Here's the updated workbook with the Budget sheet


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