Carry over totals to new worksheets

Pino1

New Member
Joined
Feb 11, 2009
Messages
6
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

BrighterLater

Board Regular
Joined
Sep 16, 2005
Messages
113
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
 
Joined
Jul 30, 2006
Messages
3,656
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

New Member
Joined
Feb 11, 2009
Messages
6
Thanks for looking at my problem. This is a screenshot of the first work week sheet.
Excel Workbook
ABCDEFGHIJKLMNOP
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
1214151617181920HoursRateExpenseWages
13Lafleche, Aurele0.01.02.03.04.00.010.025.001.27317.81
14Pancer, Robert0.08.06.00.04.00.018.013.001.21283.89
15Depatie, Robert0.03.08.00.00.00.011.018.001.21240.22
16Allaire, Shawn0.08.06.00.04.00.018.018.001.21393.08
17Labrecque, Robert0.00.00.00.08.00.08.024.001.22234.86
18    
19    
20    
21    
22    
23    
2465.0$ 1,469.86
25
26Totals From Previous Weeks:0$ -
27Totals To Date:Man-Hrs65.0Wages:$ 1,469.86
28
29
30Bills & Packing Slips:
31
Week (1)


I will post the second sheet.
 

Pino1

New Member
Joined
Feb 11, 2009
Messages
6

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQ
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
1221222324252627HoursRateExpenseWages
13Lafleche, Aurele8.08.04.00.00.00.020.025.001.27635.61
14Pancer, Robert8.08.04.00.00.00.020.013.001.21315.44
15Depatie, Robert8.00.00.00.00.00.08.018.001.21174.70
16Allaire, Shawn8.00.00.00.00.00.08.018.001.21174.70
17Labrecque, Robert8.00.00.00.00.00.08.024.001.22234.86
18    
19    
20    
21    
22    
23    
2464.0$ 1,535.31
25
26Totals From Previous Weeks:65.0$ 1,469.86
27Totals To Date:Man-Hrs129.0Wages:$ 3,005.17
28
29
30Bills & Packing Slips:
31
Week (2)


Thanks for your help.
 
Joined
Jul 30, 2006
Messages
3,656
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
 
Last edited:

Pino1

New Member
Joined
Feb 11, 2009
Messages
6

ADVERTISEMENT

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
 
Joined
Jul 30, 2006
Messages
3,656
Pino1,

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


Excel Workbook
ABCDEFGHIJKLM
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
12?HoursRateExpenseWages
13Lafleche, Aurele #NAME?#NAME? 
14Pancer, Robert #NAME?#NAME? 
15Depatie, Robert #NAME?#NAME? 
16Allaire, Shawn #NAME?#NAME? 
17Labrecque, Robert #NAME?#NAME? 
18 #NAME?#NAME? 
19 #NAME?#NAME? 
20 #NAME?#NAME? 
21 #NAME?#NAME? 
22 #NAME?#NAME? 
23 #NAME?#NAME? 
240$ -
25
26Totals From Previous Weeks:
27Totals To Date:Man-Hrs0Wages:$ -
28
29
30Bills & 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
ABCDEFGHIJKLM
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
12HoursRateExpenseWages
13Lafleche, Aurele10#NAME?#NAME?#NAME?
14Pancer, Robert18#NAME?#NAME?#NAME?
15Depatie, Robert11#NAME?#NAME?#NAME?
16Allaire, Shawn18#NAME?#NAME?#NAME?
17Labrecque, Robert8#NAME?#NAME?#NAME?
18 #NAME?#NAME? 
19 #NAME?#NAME? 
20 #NAME?#NAME? 
21 #NAME?#NAME? 
22 #NAME?#NAME? 
23 #NAME?#NAME? 
2465#NAME?
25
26Totals From Previous Weeks:
27Totals To Date:Man-Hrs65Wages:#NAME?
28
29
30Bills & 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
ABCDEFGHIJKLM
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
1214151617181920HoursRateExpenseWages
13Lafleche, Aurele01234010251.27317.81
14Pancer, Robert08604018131.21283.89
15Depatie, Robert03800011181.21240.22
16Allaire, Shawn08604018181.21393.08
17Labrecque, Robert0000808241.22234.86
18
19
20
21
22
23
2465$ * 1,469.86
25
26Totals From Previous Weeks:0$ * * * * * * * - *
27Totals To Date:Man-Hrs65Wages:$ * 1,469.86
28
29
30Bills & Packing Slips:
Week (1)




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


Excel Workbook
ABCDEFGHIJKLM
11EmployeeSunMonTueWedThuFriSatTotalPayPayrollTotal
12?HoursRateExpenseWages
13Lafleche, Aurele #NAME?#NAME? 
14Pancer, Robert #NAME?#NAME? 
15Depatie, Robert #NAME?#NAME? 
16Allaire, Shawn #NAME?#NAME? 
17Labrecque, Robert #NAME?#NAME? 
18 #NAME?#NAME? 
19 #NAME?#NAME? 
20 #NAME?#NAME? 
21 #NAME?#NAME? 
22 #NAME?#NAME? 
23 #NAME?#NAME? 
240$ -
25
26Totals From Previous Weeks:65$ * 1,469.86
27Totals To Date:Man-Hrs65Wages:#VALUE!
28
29
30Bills & 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).


Code:
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

New Member
Joined
Feb 11, 2009
Messages
6
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

New Member
Joined
Feb 11, 2009
Messages
6
Hi Stan,

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

Forum statistics

Threads
1,143,654
Messages
5,720,090
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top