Adding weekly totals from one cell to an overall total

[Chilliflake]

Hi everybody,

I am creating a League Table for recurring issues in work. It's almost finished, but I don't know how to add the "total weekly figures" from its cell to the destination cell on the main sheet, "overall total", without creating a new weekly column for each week's data.

Is it possible to have a "weekly figures" sheet that is cleared every week, but the figures entered from the "total weekly figures" cell the previous week are not deleted from the destination "overall total" cell, but the new week's figures are instead added to it as a running total, as the people who will be entering weekly figures are not overly Excel-savvy and just want to enter figures into a simple weekly sheet.

Thank you for any help you can offer, have hit a wall as this is way beyond my Excel capabilities.

CF

 

[Chilliflake]

Clearer ... working on it.

Thank you! I would never make a teacher!

 

[Logit]

See if the following works :

Sub SumAndCopyValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dateHeader As Range
    Dim targetCell As Range
    Dim sumValue As Double
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet A") ' Change "Sheet A" to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    For i = 4 To lastRow
        sumValue = Application.WorksheetFunction.Sum(ws.Range("B" & i & ":F" & i))
        ws.Range("G" & i).Value = sumValue
        
        Set dateHeader = ws.Range("J3:T3").Find(ws.Range("F1").Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not dateHeader Is Nothing Then
            Set targetCell = ws.Cells(i, dateHeader.Column)
            targetCell.Value = sumValue
        End If
        
        ws.Range("U" & i).Value = Application.WorksheetFunction.Sum(ws.Range("J" & i & ":T" & i))
    Next i
End Sub

 

[Chilliflake]

See if the following works :

Sub SumAndCopyValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dateHeader As Range
    Dim targetCell As Range
    Dim sumValue As Double
    Dim i As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet A") ' Change "Sheet A" to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
   
    For i = 4 To lastRow
        sumValue = Application.WorksheetFunction.Sum(ws.Range("B" & i & ":F" & i))
        ws.Range("G" & i).Value = sumValue
       
        Set dateHeader = ws.Range("J3:T3").Find(ws.Range("F1").Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not dateHeader Is Nothing Then
            Set targetCell = ws.Cells(i, dateHeader.Column)
            targetCell.Value = sumValue
        End If
       
        ws.Range("U" & i).Value = Application.WorksheetFunction.Sum(ws.Range("J" & i & ":T" & i))
    Next i
End Sub

Wow.. okay, this is incredible and way above my skill level. How do I enter this? Thank you..

 

[Logit]

Paste the macro into a regular module. Paste a command button on the sheet and connect it to the macro.

A change needs to be made to your worksheet. The merged cells in B1:G1 need to be unmerged. The title "Week Beginning :" goes in
cell C1. The current date goes in F1. The format for the date must be "mm/dd/yyyy" The macro code depends on these changes to work
properly.

 

[Chilliflake]

Paste the macro into a regular module. Paste a command button on the sheet and connect it to the macro.

A change needs to be made to your worksheet. The merged cells in B1:G1 need to be unmerged. The title "Week Beginning :" goes in
cell C1. The current date goes in F1. The format for the date must be "mm/dd/yyyy" The macro code depends on these changes to work
properly.

Hi,

How do I do the paste and command? Sorry, my level of Excel is pretty beginner..

We write the date here in Ireland as did/mm/yyyy. Would the macro work if it was changed to that format? The people using it will enter the date in this format without thinking. Otherwise I will change the format of the date cells to the one you instructed and leave a note on the sheet for the date to be entered as you stated.

Thank you again

 

[Logit]

Right click sheet tab, select VIEW CODE
From the top menu, click INSERT
Select MODULE
Paste the macro, SumAndCopyValues, in the large white window on the right side
Click the CLOSE X in the upper right corner
Click the Excel OPTIONS button in the upper left corner
Click EXCEL OPTIONS
Click POPULAR
Click the checkbox "SHOW DEVELOPER TAB IN THE RIBBON"
Click OK
You should now see a DEVELOPER tab in the top menu bar
Click on the DEVELOPER tab
Click INSERT
In the upper left corner of the small FORM CONTROLS, click the small button in the upper left corner
Place your cursor anywhere in the sheet and left click
The Command Button is now pasted to the worksheet and another window opens showing the macros your workbook contains
Click on the macro name in the small window, then click OK
The command button is now attached to the macro
Every time you click the command button it will run that macro

You can move the button by right clicking on it and creating a 'fuzzy' outline
Move your cursor over the button until you see four small arrows pointing in different directions
Left click and hold, then drag the button where you want it

In the future, now that you have created the DEVELOPER tab, you can get to the programming VBE windows by
clicking on the DEVELOPER TAB, then VISUAL BASIC on the left side. That will open the programming VBE

Regarding the formatting of dates in the workbook. You can definitely utilize the European format but keep in mind that all dates must be in the same format throughout the workbook

 

[Chilliflake]

Right click sheet tab, select VIEW CODE
From the top menu, click INSERT
Select MODULE
Paste the macro, SumAndCopyValues, in the large white window on the right side
Click the CLOSE X in the upper right corner
Click the Excel OPTIONS button in the upper left corner
Click EXCEL OPTIONS
Click POPULAR
Click the checkbox "SHOW DEVELOPER TAB IN THE RIBBON"
Click OK
You should now see a DEVELOPER tab in the top menu bar
Click on the DEVELOPER tab
Click INSERT
In the upper left corner of the small FORM CONTROLS, click the small button in the upper left corner
Place your cursor anywhere in the sheet and left click
The Command Button is now pasted to the worksheet and another window opens showing the macros your workbook contains
Click on the macro name in the small window, then click OK
The command button is now attached to the macro
Every time you click the command button it will run that macro

You can move the button by right clicking on it and creating a 'fuzzy' outline
Move your cursor over the button until you see four small arrows pointing in different directions
Left click and hold, then drag the button where you want it

In the future, now that you have created the DEVELOPER tab, you can get to the programming VBE windows by
clicking on the DEVELOPER TAB, then VISUAL BASIC on the left side. That will open the programming VBE

Regarding the formatting of dates in the workbook. You can definitely utilize the European format but keep in mind that all dates must be in the same format throughout the workbook

Hi,

I’ve done as instructed but not quite sure if what I’ve done is correct. Would you please check it out for me. The macro doesn’t work yet so maybe I made an error.. Thank you as always

Dropbox

www.dropbox.com

 

[Chilliflake]

Hi,

I’ve done as instructed but not quite sure if what I’ve done is correct. Would you please check it out for me. The macro doesn’t work yet so maybe I made an error.. Thank you as always

Dropbox

www.dropbox.com

One other thing - I need to change the sheet names from Sheet A to MSQs and Sheet B to TISMA.

 

[Logit]

You did everything correct. There is a correcting to the macro. Copy paste the following into the VBE ... remove the first macro.

Sub SumAndCopyValues()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim dateHeader As Range

    Dim targetCell As Range

    Dim sumValue As Double

    Dim i As Long

  

    Set ws = ThisWorkbook.Sheets("MSQs") ' Change "Sheet A" to your sheet name

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

  

    For i = 4 To lastRow

        sumValue = Application.WorksheetFunction.Sum(ws.Range("B" & i & ":F" & i))

        ws.Range("G" & i).Value = sumValue

      

        Set dateHeader = ws.Range("J3:T3").Find(ws.Range("F1").Value, LookIn:=xlValues, LookAt:=xlWhole)

        If Not dateHeader Is Nothing Then

            Set targetCell = ws.Cells(i, dateHeader.Column)

            targetCell.Value = sumValue

        End If

      

        On Error Resume Next

      

        ' Correcting the error by checking if the range is valid before summing

        If Application.WorksheetFunction.CountA(ws.Range("J" & i & ":T" & i)) > 0 Then

            ws.Range("U" & i).Value = Application.WorksheetFunction.Sum(ws.Range("J" & i & ":T" & i))

        Else

            ws.Range("U" & i).Value = 0

        End If

    Next i

End Sub

 

[Chilliflake]

Do I leave in the green text or are they instructions for me? I've changed Sheet A to MSQs and run the macro but nothing is happening... Sheet B will be a copy of Sheet A for a different department so that can be ignored for the moment.