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]

Mike, I'm no good with formulas so you'll need to start a new thread to get the formulas from the previous year for the budget sheet.

As for creating and naming a new workbook for the upcoming fiscal year I would approach it differently.
See if this would work for you. It's your workbook from post 10 emptied out and used as a template.
Unfortunately we both need assistance to set up the formulas on the budget sheet.
Hopefully deriving the previous fiscal year file name at the end of the macro will be of some use.

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

 

[mikecox39]

Mike, I'm no good with formulas so you'll need to start a new thread to get the formulas from the previous year for the budget sheet.

As for creating and naming a new workbook for the upcoming fiscal year I would approach it differently.
See if this would work for you. It's your workbook from post 10 emptied out and used as a template.
Unfortunately we both need assistance to set up the formulas on the budget sheet.
Hopefully deriving the previous fiscal year file name at the end of the macro will be of some use.

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

The template is a valid idea but the workbook is frequently tweaked during the year so using the previous method keeps the current workbook, with the tweaks.

Thanks for your help with this. i learned a lot on the process. I will post a new question with regards formula techniques for pulling in data and name options.

 

[NoSparks]

Hello Mike

Finished my Grandpa duties for the weekend and came back for another look at this.

Think I now know how to deal with the formulas for the budget sheet but need you to verify before I incorporate into a completed
'Get Ready for the New Year' macro.

I copied the Budget sheet from your last linked file to a new workbook, have used columns P, Q, R, S, T and U as helper columns.
This Budget sheet differs from the one in your original linked file by having Legal fees and Misc so I don't know what's actually in the file you're currently using.
Have assumed the file you use now is MB18.xlsm and hard coded it into Q1 for test purposes, if that's wrong change it to actual name.

This file needs to be in the same folder as MB18,
running the 'Testing' macro should put the appropriate formulas into the column B cells and get the data without opening MB18.
You need to check all the results, especially B9 and where it gets its data from.

https://app.box.com/s/4wfi5lfyncu3dotql30sd00sibbydpdj

 

[mikecox39]

Hello Mike

Finished my Grandpa duties for the weekend and came back for another look at this.

Think I now know how to deal with the formulas for the budget sheet but need you to verify before I incorporate into a completed
'Get Ready for the New Year' macro.

I copied the Budget sheet from your last linked file to a new workbook, have used columns P, Q, R, S, T and U as helper columns.
This Budget sheet differs from the one in your original linked file by having Legal fees and Misc so I don't know what's actually in the file you're currently using.
Have assumed the file you use now is MB18.xlsm and hard coded it into Q1 for test purposes, if that's wrong change it to actual name.

This file needs to be in the same folder as MB18,
running the 'Testing' macro should put the appropriate formulas into the column B cells and get the data without opening MB18.
You need to check all the results, especially B9 and where it gets its data from.

https://app.box.com/s/4wfi5lfyncu3dotql30sd00sibbydpdj

Hey GramPa!

I think I see your issue, the categories in the Master sheet don't match the categories in the Budget sheet; they are out of order and 2 are missing. I've reentered them so they match up and are in the same order. Will that fix the issue?

I've attached the updated this years budget file, MB18. I added a module with the clear data macro you so kindly provided, with a couple modifications. It's works perfectly! Where should that be added to the new code, so it all works together?

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

 

[NoSparks]

Okay, have changed your Budget sheet to be what I think you want.
Removed one Taxes row and one of the Landscape rows.
Items are ordered as they appear on Master sheet, although that was not critical.
The important thing is columns T & U on the Budget sheet as these are the paired cells of where the formula goes and what it's linked to,
Column S is just for clarity on the T & U cells, these 3 columns were manual input.
Cells P1, Q1 and R1 are populated by the VBA and used in the formulas for path to the 'old' Master sheet.

Have removed the BackupBudget module as its file names are/will be wrong.
Replaced the Clear_Data module with NewYearSetUp module.

Hopefully this does the job.

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

 

[mikecox39]

Okay, have changed your Budget sheet to be what I think you want.
Removed one Taxes row and one of the Landscape rows.
Items are ordered as they appear on Master sheet, although that was not critical.
The important thing is columns T & U on the Budget sheet as these are the paired cells of where the formula goes and what it's linked to,
Column S is just for clarity on the T & U cells, these 3 columns were manual input.
Cells P1, Q1 and R1 are populated by the VBA and used in the formulas for path to the 'old' Master sheet.

Have removed the BackupBudget module as its file names are/will be wrong.
Replaced the Clear_Data module with NewYearSetUp module.

Hopefully this does the job.

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

Ok, got it.

Now I need some time to sort it out. Which means I am about to open the macro and study it, and your notes, in an effort to appreciate how awesome it is, and to understand how it works. That will will take some time and most certainly generate questions, which I hope you would be willing to indulge.

As I look at the Budget sheet I notice that column B has manual entries, with this years data. I expect when the macro runs on the last day of the month it will get the numbers from last years Budget sheet and plug them into MB19, that it will create. I'm guessing Column T has something to do with what will end up in MB19's Column B.

Thank-you for doing this, I hope you won't rue the day you got involved

 

[NoSparks]

As I look at the Budget sheet I notice that column B has manual entries, with this years data

Those are numbers that existed on that sheet in the file you last supplied. I don't know where they came from, they are under Fiscal 7/1/2016 and will get replaced with data from MB18 Master. But the saved MB18 will have them on that Budget sheet. You could delete them before running the macro if that's an issue.

I'm guessing Column T has something to do with what will end up in MB19's Column B.

the macro loops through column T for the cell to put the formula in, and the adjacent cell in column U tells the macro which cell the formula is to use on the previous year Master sheet.

I hope you won't rue the day you got involved

Me too, actually I just hope we get it working for you.

 

[mikecox39]

Those are numbers that existed on that sheet in the file you last supplied. I don't know where they came from, they are under Fiscal 7/1/2016 and will get replaced with data from MB18 Master. But the saved MB18 will have them on that Budget sheet. You could delete them before running the macro if that's an issue.

the macro loops through column T for the cell to put the formula in, and the adjacent cell in column U tells the macro which cell the formula is to use on the previous year Master sheet.


Me too, actually I just hope we get it working for you.

Thanks for the clarification.

I will try my best to work this out on my own, I don't want to wear out my welcome (-:

 

[mikecox39]

I just ran the macro you created for me and it went will until it popped up a d.docs. window requesting a password. I tried my MS Live password but it didn't work so I don't know what PW it wants. I'm also concerned that the person taking over from my will have the same problem. Anyway to avoid that pop up?

 

[NoSparks]

I have no idea what d.docs is and doubt the request is coming from Excel as nothing I worked with has any password.

A Google search for d.docs indicates it's something to do with Microsoft's OneDrive, which I don't use so
sorry, I'm afraid I can't help with that.