Automate date range entry

mikecox39

Active Member
Joined
Mar 5, 2014
Messages
251
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top