Solved weekly data to a ytd running total

September 7, 2011 at 10:44:55
Specs: Windows 7
I have a workbook with two primary sheets. Sheet1 is where the weekly data is entered. I want sheet2 to take the weekly totals and create a ytd figure, that repopulates as new data is entered in the old total row. I have been trying to find a formula that will let me do this.
In sheet1 I am totaling data in colums, each column totals at row 33 (and includes data from rows 8 to 32) in columns (C, D, E, F, G) I need each column to remain seperate since each column represents the item being counted.
I am trying to get each columns totals to copy to sheet 2 and store the ytd figures. This book is only a weekly log but I am trying to make it so I can track the totals being inputed. So each week employees will go into the workbook and enter in their weekly data, click save and email the book to me. I need to ytd data so I can show the client the overall numbers, not just the weekly numbers that sheet 1 shows.

See More: weekly data to a ytd running total

Report •

✔ Best Answer
September 7, 2011 at 15:13:46
Where you put the code should not make any difference - except in the ThisWorkbook module. Don't put it there.

You know that the code has to be run, right? Nothings happens automatically. There are many ways to run the code but the simplest is to click anywhere in the code so that you see the flashing cursor somewhere within the body of the macro. Then click the little green triangle above the word Run in the toolbar. Eventually you can attach it to a button in your workbook to make it easy to run.

I opened a new workbook and renamed 2 sheets, one to be "Data entry sheet" and one to be "Job YTD".

In C33 of "Data entry sheet" I entered 25.

I copied your code into a module and clicked the Run arrow.

I ended up with 25 in 'Job YTD'!C8 as expected.

I clicked the Run arrow 3 more times and ended up with 100 in 'Job YTD'!C8 since it added 25 to the value in C8 each time (4*25 = 100)

There's nothing wrong with your code, so you'll have to look elsewhere for the problem.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 7, 2011 at 12:03:08
If the original weekly numbers are being replaced, then you can't use a formula to retain any values that are (was) based on the numbers that are no longer there.

It could be done using VBA, but making it automatic would be tricky - but not impossible.

If clicking a button is OK, something could be written to add the current weekly data (e.g. C33)to an existing "hard value" in Sheet 2 and replace the "hard value" with an updated one:

e.g. Let's say you have a value in Sheet2!C2. This code will add the value in Sheet1!C33 to the value in Sheet2!C2 each time you run it:

Sub UpdateSheet2()
   Sheets(2).Range("C2") = _
       Sheets(2).Range("C2") + Sheets(1).Range("C33") 
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 7, 2011 at 12:31:27
DerbyDad,
Thanks for getting back to me, I am not very advanced with excel, but I have learned how to do what you suggested.

I pasted your code in and made 2 changes: changed "C2" to "C8"

Sub UpdateSheet2()
Sheets(2).Range("C8") = _
Sheets(2).Range("C8") + Sheets(1).Range("C33")
End Sub

it is not bringing the information over from sheet1, by the way I renamed my sheets to match but my sheets will be named - sheet1 "Data entry sheet" and sheet sheet6 "Job YTD" I have sheeets 2, 3, 4, 5 that are not used in this just as tabs with associated graphs. I am not sure if that is the issue but I am fixing to delete these other sheets and try it again.
Can I have a 0 in sheet2 C2 as the hard value?


Report •

#3
September 7, 2011 at 12:59:52
Sheets(1) and Sheets(2) refer to the first and second sheets in the workbook, based on the tab Order, regardless of the name on the tab.

Since you mentioned Sheet1 and Sheet2 in your OP, I had no reason to doubt which sheets you were working with.

Since it appears that you are actually working with Sheet1 and Sheet6, change each occurrence of Sheets(2) to be Sheets(6) and you should be good to go - as long as you don't rearrange or delete or add any sheets that changes the positions of those sheets.

If you don't want to be concerned with the position of the sheets, refer to them by name:

Sheets("Data entry sheet") and Sheets("Job YTD")

The name must be enclosed in quotes and must match the name on the tab exactly.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 7, 2011 at 13:10:18
I stated it as simply as I could to start with, but thanks for figuring out what I was talking about.

Do I paste your code under "view code" for a specific sheet or do I select the thisworkbook section.

Sorry I am still learning about the vb elements and I have a long way to go.


Report •

#5
September 7, 2011 at 13:57:26
DerbyDad,
I have tried it every way I can think of and it is simply not taking the total from sheet 1, c33 and putting it in sheet 6, c8. I am resending what I have down. Will you please double check the info. Also I am putting a 0 in sheet 6, c8 to start things off.

Sub UpdateSheet6()
Sheets("Job YTD").Range("C8") = _
Sheets("Job YTD").Range("C8") + Sheets("Data entry sheet").Range("C33")
End Sub

Even after I figure this one out I have 6 more, will all of these need individual codes or can I combine them?


Report •

#6
September 7, 2011 at 13:57:51
You could put the code in a sheet module by using View Code but it would be better if it was in a regular module.

Choose View Code or use Alt-F11 to get into the VBA editor. Then click Insert...Module and paste the code there.

Obviously you'll have to replicate the code for each column, C through G. That can be done all within the same macro.

BTW...Before posting any more code in this forum, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
September 7, 2011 at 14:11:25
I will try it tomorrow since my work day is almost over. Thanks for your help, but unless changing where I input the code makes a major difference I am not having any luck but I will let you know tomorrow.

I thought that was instructions on how to post it in my sheet. I will ensure I post accordingly from now on.


Report •

#8
September 7, 2011 at 15:13:46
✔ Best Answer
Where you put the code should not make any difference - except in the ThisWorkbook module. Don't put it there.

You know that the code has to be run, right? Nothings happens automatically. There are many ways to run the code but the simplest is to click anywhere in the code so that you see the flashing cursor somewhere within the body of the macro. Then click the little green triangle above the word Run in the toolbar. Eventually you can attach it to a button in your workbook to make it easy to run.

I opened a new workbook and renamed 2 sheets, one to be "Data entry sheet" and one to be "Job YTD".

In C33 of "Data entry sheet" I entered 25.

I copied your code into a module and clicked the Run arrow.

I ended up with 25 in 'Job YTD'!C8 as expected.

I clicked the Run arrow 3 more times and ended up with 100 in 'Job YTD'!C8 since it added 25 to the value in C8 each time (4*25 = 100)

There's nothing wrong with your code, so you'll have to look elsewhere for the problem.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
September 8, 2011 at 04:44:50
Thanks for the help. I did not know how to run the code and had to learn. Is there an easier way to run the code? Right now I have to go into view code, close the input box, then click the run tab then click run macros, I am sure I am doing it way wrong but at least it is working.

One last question and I will be done. When I add the other rows do I just type a new code for each one like you did and just paste it below the one you origionally did for me. If so do I need to change anything, or can I just add the info I want summed, like next to c8 in the code add c8:h8, or c8, d8, e8...

Thanks for all your help.


Report •

#10
September 8, 2011 at 05:38:31
DerbyDad,
I figured out how to apply your code to the rest of the columns. Thanks so much.

Now if you can just help me figure out how to make the macros easier to run. You mentioned I could attach it to a button in my workbook. I have no idea how to accomplish this.

Thanks and your code works just like you said it would.


Report •

Ask Question