VBA To Update Date and Clear Cells

So what you want is a rolling 13-months. In other words. Since we are now in August 2016, you want to keep August 2015 and all months in between. So when September 1, 2016 arrives, I drop August 2015 and the calendar will run from September 2015 to September 2016 with September 2016 being the "working" month. Is this correct?

I built this one on top of the old one. The old code is still there but not used.

Here is how this one works. When you open the spreadsheet, it gets the current date and backs it up to the first of the month. It then compares that date to the date in cell A21. If it's the same month, nothing happens except for row 9. Suppose you last opened the book on Friday August 12th and today is now Tuesday August 16. The code will go through an clear all entries from August 1, 2015 to August 16, 2015. This keeps only 365 days of data in the calculations.

September 1, 2016 comes around and now the date in Cell A21 is less than the first of September, so August 2015 goes away and everything is copied up one row. So September 2015 is now the top row. September 2016 is added as the last row. In addition, there is a check to see how many days are in September 2016 and the extra day (the 31st) is grayed out. These shadings get copied up each month.

I notice how you have other pages in this workbook. I am not sure how the changes affect them.

I'll take a look. I got this in my "to do" bin.

I think I got this.

All the functionality is in the macro called Month_Shift.

This macro checks cell A9 to see if it is more than a year out of date. If it is, it will shift everything up and then add a new line to the end. The new line will be six months from a year after the former value of cell A9. So if you skip a month or two, the application will catch up. In theory, it should catch up no matter what the start date in cell A9 is. So if you are saving this as a template with Aug 2015 as the date, run the application first, and then start data entry.

After that it goes through and shades calendar dates that don’t exist like Feb 30th.

Then it goes through and clears out dates older than same date last year. So if today were Aug 15, 2016, it would zero out all dates prior to Aug 5,2015.

You may want to check the formulas in Cells R3 and R4. I don’t think they point to anything useful.

I've done some QA on this and it seems to work. You might want to "wring it out."

I downloaded the file from the link. I opened it up and it shows the dates from Aug 2015 to Feb 2017. That's not surprising since I have an on open workbook event.

As a test, I changed the dates to start in July 2015 instead of August 2015 - this made it end in January 2017. Then I ran the macro and it shifted everything up and added February 2017.

It appears to be working. This is what I think it is supposed to be doing.

If it is not working for you, check to make sure you have the macros enabled.

The way it is set up is to create one month into the future. I think it would be a relatively minor tweak to the code to go further into the future. How for would you like to go? If I ran the report today, what is the latest month you'd want to see?

I think this will do it for you. I've done almost no QA on this. I checked to see that the dates copied up, however, I suggest that you fill in some data and make sure they copy up too.