Help creating a macro that adds the previous day's data into a monthly column.

**MonaHussain** · 06-25-2012, 03:26 PM

Hi,

I want to create a macro effectively called "Update Stats" that will add the previous day's stats into a monthly total column.

The spreadsheet contains 7 days running total, with the 1st column dropping off, after adding a new one, currently the data wont get saved, but i want all data saved in a monthly column rather than dropping off. It would also prevent me adding the monthly column manually.

Would there also be a way that will archive the daily figures in a seperate sheet in the spreasheet?

I have attached an example of how my spreasheet would look, the "New Day" macro, deletes the 1st column and adds a new one, therefore, no data is getting saved.

If you need more information please let me know.

Thanks for the help!! :-)

Mona

**jeffreybrown** · 06-26-2012, 11:59 AM

Hi Mona,

Here's a suggestion. Slightly different and more manageable than your initial approach.

Having weeks drop off and slide over seems like some trouble, so why not just keep all the year's data and do some calculations from there. With this method, row 1 contains all of the day's throughout the year with the day of the week in row 2. You could also set this up to go vertical instead of horizontal.

Column HH contains the week's total with the week defined in HJ2 (Today) and HK2 (a full week from Today). Column H1 contains the current month's total.

I used conditional formatting to highlight the weekends, but if you don't need this just delete the conditional formatting.

Lastly, the macro. Right click on Sheet1 >> View code

Every time you activate the worksheet the macro will hide all rows that do not match HJ2 and HK2. On worksheet deactivate all columns are unhidden and ready to hide again on activate. When tomorrow comes, the macro will take care of hiding all but the week's worth.

Let me know is this is something you can work with.

**MonaHussain** · 06-28-2012, 08:32 AM

Hi Jeff,

Thanks for getting back to me.

I will take a look at your suggestions and see if it is something i can work with and get back to you.

Thanks again

Mona