Carry over totals to new worksheets

[Pino1]

I created a time sheet for employees which calculates the total wages each week. I copy the worksheet to create a new time sheet for each week. I want all subsequent time sheets (worksheets) after the first one to calculate the total wages to date by adding the total wages of the current sheet to the total on the previous sheet. If for example I am on Week 2, I know how to reference the wages cell from the previous week by using something like ='Week (1)'!M28, but when I copy the Week 2 sheet to create Week 3, the new sheet still references Week 1. I don't want to manually change the sheet number each week. How can I make it reference the previous week automatically?

 

[BrighterLater]

Hi

Noticed you hadn't had a reply. Maybe this might help in the absence of help from others.

Suggest you play with this in a new work book.

Step 1:

You have to know how to insert a module in the VB Editor and create a Public function in it.

Public Function lastweek() As Variant
Dim addrs As String

' Get addres of active cell

addrs = ActiveCell.Address

' Get value of Cell in the sheet immediatley before this in Worksheets Collection at the same
' Address as the activecell

lastweek = Worksheets((ActiveSheet.Index) - 1).Range(addrs).Value
End Function

Step2:

Go to Sheet1 (which will be Worksheets(1))
Put 6 in $A$4

Step3:

Go to $A$4 in Sheet2 (which will be Worksheets(2)) and put

=lastweek() + 9

You should get a result of 15.

I hope that you can then apply this to your particular situation

 

[stanleydgromjr]

Pino1,

Welcome to the MrExcel board.

Can we have a screenshot of the first worksheet (containing sample data), and the next sheet with the totals carried forward?

What is the naming convention for each successive new sheet?


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Pino1]

Thanks for looking at my problem. This is a screenshot of the first work week sheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12			14	15	16	17	18	19	20	Hours	Rate	Expense	Wages
13	Lafleche, Aurele			0.0	1.0	2.0	3.0	4.0	0.0	10.0	25.00	1.27	317.81
14	Pancer, Robert			0.0	8.0	6.0	0.0	4.0	0.0	18.0	13.00	1.21	283.89
15	Depatie, Robert			0.0	3.0	8.0	0.0	0.0	0.0	11.0	18.00	1.21	240.22
16	Allaire, Shawn			0.0	8.0	6.0	0.0	4.0	0.0	18.0	18.00	1.21	393.08
17	Labrecque, Robert			0.0	0.0	0.0	0.0	8.0	0.0	8.0	24.00	1.22	234.86
18
19
20
21
22
23
24										65.0			$ 1,469.86
25
26	Totals From Previous Weeks:									0			$ -
27	Totals To Date:							Man-Hrs		65.0		Wages:	$ 1,469.86
28
29
30	Bills & Packing Slips:
31
Week (1)

I will post the second sheet.

 

[Pino1]

This is the second work week sheet. As you can see I reference the previous week's totals (sheet 1) but if I copy this sheet to create week 3 I will have to manually change the sheet number referenced (from sheet 1 to sheet 2).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12			21	22	23	24	25	26	27	Hours	Rate	Expense	Wages
13	Lafleche, Aurele			8.0	8.0	4.0	0.0	0.0	0.0	20.0	25.00	1.27	635.61
14	Pancer, Robert			8.0	8.0	4.0	0.0	0.0	0.0	20.0	13.00	1.21	315.44
15	Depatie, Robert			8.0	0.0	0.0	0.0	0.0	0.0	8.0	18.00	1.21	174.70
16	Allaire, Shawn			8.0	0.0	0.0	0.0	0.0	0.0	8.0	18.00	1.21	174.70
17	Labrecque, Robert			8.0	0.0	0.0	0.0	0.0	0.0	8.0	24.00	1.22	234.86
18
19
20
21
22
23
24										64.0			$ 1,535.31
25
26	Totals From Previous Weeks:									65.0			$ 1,469.86
27	Totals To Date:							Man-Hrs		129.0		Wages:	$ 3,005.17
28
29
30	Bills & Packing Slips:
31
Week (2)

Thanks for your help.

 

[stanleydgromjr]

Pino1,

Thanks for the screenshots.

Just thinking out loud.

You could create a sheet called "Template".

Template would have all the information (formats, names, formulae) as "Week (1)", except for the days in row 12, the hours worked per day/per employee, and prior weeks totals in row 27.

You would run a macro, say "CreateNextWeek", and it could:
1. ask for the next weeks number (this could also be automated)
2. copy "Template" into the next weeks sheet.........
3. it would be able, from the new sheet name, pull J27 and M27 from the prior weeks sheet
4. it could also put the cursor in cell C12 with a message box asking you to manuall enter the numbers for the days (this could also be automated)

How are your worksheets ordered, left to right?

Where is the new week sheet in the above order?

What version of Excel are you using?


Have a great day,
Stan

 

[Pino1]

Thanks Stan,

Your idea sounds interesting...may be worth a try. I'm not sure about the CreateNextWeek macro (may need help on this one as I am fairly new at code).

My worksheets are ordered left to right and named Week 1, Week 2, etc. Therefore, for example, I create Week 3 by simply copying the Week 2 worksheet and placing it at the end (right).

I'm using excel 2003 so I don't think I can have a drop down calendar for my dates. I am working on trying to automate the days of the week based on the month entered.

Again, thanks for your interest in my problem.

Pino

 

[stanleydgromjr]

Pino1,

OK, here is what I have so far (for your testing).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12			?							Hours	Rate	Expense	Wages
13	Lafleche, Aurele										#NAME?	#NAME?
14	Pancer, Robert										#NAME?	#NAME?
15	Depatie, Robert										#NAME?	#NAME?
16	Allaire, Shawn										#NAME?	#NAME?
17	Labrecque, Robert										#NAME?	#NAME?
18											#NAME?	#NAME?
19											#NAME?	#NAME?
20											#NAME?	#NAME?
21											#NAME?	#NAME?
22											#NAME?	#NAME?
23											#NAME?	#NAME?
24										0			$ -
25
26	Totals From Previous Weeks:
27	Totals To Date:							Man-Hrs		0		Wages:	$ -
28
29
30	Bills & Packing Slips:
Template

When you start the "CreateNextWeek" macro, you are asked for the next/new weeks number, and for the new sheet "Week (1)":

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12										Hours	Rate	Expense	Wages
13	Lafleche, Aurele									10	#NAME?	#NAME?	#NAME?
14	Pancer, Robert									18	#NAME?	#NAME?	#NAME?
15	Depatie, Robert									11	#NAME?	#NAME?	#NAME?
16	Allaire, Shawn									18	#NAME?	#NAME?	#NAME?
17	Labrecque, Robert									8	#NAME?	#NAME?	#NAME?
18											#NAME?	#NAME?
19											#NAME?	#NAME?
20											#NAME?	#NAME?
21											#NAME?	#NAME?
22											#NAME?	#NAME?
23											#NAME?	#NAME?
24										65			#NAME?
25
26	Totals From Previous Weeks:
27	Totals To Date:							Man-Hrs		65		Wages:	#NAME?
28
29
30	Bills & Packing Slips:
Week (1)

You then enter the hours per day for each employee (sheetnames on the screenshots may differ clightly for these examples - your VLOOKUP formulae do not compute because I do not have the "EmployeeTable"):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12			14	15	16	17	18	19	20	Hours	Rate	Expense	Wages
13	Lafleche, Aurele			0	1	2	3	4	0	10	25	1.27	317.81
14	Pancer, Robert			0	8	6	0	4	0	18	13	1.21	283.89
15	Depatie, Robert			0	3	8	0	0	0	11	18	1.21	240.22
16	Allaire, Shawn			0	8	6	0	4	0	18	18	1.21	393.08
17	Labrecque, Robert			0	0	0	0	8	0	8	24	1.22	234.86
18
19
20
21
22
23
24										65			$ * 1,469.86
25
26	Totals From Previous Weeks:									0			$ * * * * * * * - *
27	Totals To Date:							Man-Hrs		65		Wages:	$ * 1,469.86
28
29
30	Bills & Packing Slips:
Week (1)

Then you run the macro again, for "Week (2)", and you get:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
11	Employee		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Pay	Payroll	Total
12			?							Hours	Rate	Expense	Wages
13	Lafleche, Aurele										#NAME?	#NAME?
14	Pancer, Robert										#NAME?	#NAME?
15	Depatie, Robert										#NAME?	#NAME?
16	Allaire, Shawn										#NAME?	#NAME?
17	Labrecque, Robert										#NAME?	#NAME?
18											#NAME?	#NAME?
19											#NAME?	#NAME?
20											#NAME?	#NAME?
21											#NAME?	#NAME?
22											#NAME?	#NAME?
23											#NAME?	#NAME?
24										0			$ -
25
26	Totals From Previous Weeks:									65			$ * 1,469.86
27	Totals To Date:							Man-Hrs		65		Wages:	#VALUE!
28
29
30	Bills & Packing Slips:
Week (2)

You have to fill in the date numbers in cells C12 thru I12.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub CreateNextWeek()
    Dim WeekNbr As Long
    WeekNbr = InputBox("Enter the week number.")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Week (" & WeekNbr & ")"
    If WeekNbr = 1 Then
        Cells(26, "J") = ""
        Cells(26, "M") = ""
    Else
        Cells(26, "J").Formula = "='Week (" & WeekNbr - 1 & ")'!J27"
        Cells(26, "M").Formula = "='Week (" & WeekNbr - 1 & ")'!M27"
    End If
    Range("C12").Select
    MsgBox "Please enter the day numbers for cells C12 thru I12."
End Sub

For the above process to work you will need to create sheet "Template".

Then run the "CreateNextWeek" macro.


Have a great day,
Stan

 

[Pino1]

Stan,

I am leaving for a few days, but I will certainly check your idea and let you know how I make out.

Thanks and have a nice weekend,
Pino

 

[Pino1]

Hi Stan,

I tried your suggestion and it works great! Thanks for all your help!