Need Help! Rolling total for Last 7 Days

[.Marshall]

Hello,

I have a problem that I have not been able to solve by reviewing other questions asked before.

Overview: I have a worksheet called "Workspace" that totals the data in sheet's 1-3. Every morning, sheets 1-3 are filled with yesterdays data which are totalled using =VALUE at the top of the workspace sheet. Then I run a Macro to "Rollup" yesterdays data, which copys the "live" data, inserts a new row at the top of the chart and pastes only values (so the formulas to not come with the new row). On the next sheet labeled "Work hours total" I need to have rolling calculations of "Last 7 Days", "Totals This Month", and "Year-to-Date"

Problem: When using the formulas on this blog post to fill my tables; my "Last 7" table resets to '0' across the board every Monday, I want it to be a rolling Last 7, so at any given day I can determine what the totals are for the last 7. As is right now, that formula is more like a "Calculate this week" option.

Is there a better way to do this?

I have attached an example Workbook for your reference, it is not actually the workbook I am trying to make work because I cannot share that with you guys, but it is very similar on a much smaller scale.

Sample Company Roll-up.xlsm

[David A Coop]

Hi Marshall,

First of all, welcome to the forum, I trust you will find the help you need

Rolling last 7 days.xlsm

On your Work Hours Total sheet, I have entered formulas which I think will give you what you want.

I have also changed your macro! Instead of inserting a new row every time on the Workspace sheet, I calculate a copy range, and just copy all the data one row down, then place the values from row 1 into row 4.

Doing it this way makes it easier to have the formulas for 7 days pointing to the first 7 rows of data. The formulas are not changed by Excel as occurs when you insert rows.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[Richard Buttrey]

Hi,

You ask if there is a better way. The answer is almost certainly Yes but it involves changing your layout.

We see this sort of question all the time and you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality and find ways of analysing data you've not even thought about.

Capture your data for the three stores on one sheet with the three columns of data you now capture and two additional columns for date and store.

[.Marshall]

David, thank you! Looks great, what is the name of the Macro you assigned to the Launch button? There is only one Macro running when you click the button correct? I see 3 Macro's now.

[.Marshall]

Also, I copied the macro EXACTLY into the excel document I need it in and it displays an error when I try to run it.

"Run-time error '1004'

Application-defined or object-defined error"

When I click 'Debug' it highlights this line: LastRow = Cells(Rows.Count, "A").End(x1Up).Row

Is there anything I have to do outside of the macro in order for the macro to run correctly? Maybe something to do with establishing the copy range?


EDIT: I believe I found the problem, I can't find which Macro's are assigned to the Launch Roll-up button. Is the macro named 'rollup_old' need to be assigned to the button? Or how about the otherone named Macro2? Just need some clarification.

Thank you David

[David A Coop]

Hi Marshall,

I don't have the file open, but I renamed your macro Rollup_Old, and the new macro kept the same name you gave it Rollup which should be connected to the button. You can get rid of any other macros, I may have recorded something to test code, and forgot to delete it!



As far as the failing code is concerned, I am not sure why this code is failing, unless you don't have anything in column A of your live sheet. The code LastRow = Cells(Rows.Count, "A").End(x1Up).Row looks at Column A, and finds the row number of the last cell with data in it.
This worked in the sample you sent, is the real file different? You can change the "A" if you need to!

Regards,

David

[.Marshall]

Okay gotcha, do I need to have my old macro assigned as well? Or JUST your new macro?

When I step into the macros in that workbook your new macro is on the same sheet as my old macro. I figured I didn't need my old macro to run before your new macro so I removed my old lines of code.

I just have dates in column "A" just like in my example workbook. Should I change it to Column "B" considering that's where the real data (numbers) start?

Thanks David,

Marshall

[David A Coop]

Hi Marshall,

Get rid of all the macros other than the new one (Rollup).

As long as there is data in column A it will work, provided that data comes down to the last row. The line of code we were talking about goes to the bottom of the worksheet, then goes up until it finds something, then uses that row number.

Note that the next line of code says Range("A4:P" & LastRow).Select - if your new live data goes beyond column P you will need to adjust the code accordingly.

Please keep me posted as to how you are getting along.

FINALLY, I suggest you read and consider Richard Buttrey's comments. He is absolutely correct. The way you are capturing the data makes for difficult management reporting.

I have attached a very quick example - just keep adding your store data to an ever increasing list. This is easy to say, but I have no idea how much data would be involved. This is just an example of what I think Richard was talking about.

Rolling last 7 days 2.xlsm

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[.Marshall]

Thank you for all the help David, you have been extremely helpful! Richard I wrote a reply to you but it must not have posted, as I am not on the most reliable internet, thank you for your suggestion and insight, I appreciate all of it!

I fixed the problem after 2 full days of banging my head against the screen.. lets just say lowercase 'l' and the number '1' very similar to my un-trained eye, and given I am un-able to simply copy and paste, it was a honest mistake. Macro works great, formulas work great, thanks again David.

Problem solved, reputation awarded!

Thanks guys!

Marshall

[David A Coop]

You're not Robinson Crusoe there Marshall with the l I 1 confusion.

Pleased it works, and thanks for the the encouragement.

David