Summarizing Data from One Worksheet to Another

[Philip1957]

Greetings,

I know just enough VBA to be dangerous, and what I'm attempting is way over my head. I would be grateful for any assistance.

I have a workbook with two sheets named "Tracking" and "Summary". On the Tracking sheet in column A is a number "JobDay" (1 - 5). I want to add the values in Tracking ("M") of each row that has JobDay = 1 in it and paste the sum in Summary ("G4"). I then want to repeat this for JobDay values 2 - 5, pasting the sum in Summary H4, I4, J4, & K4 respectively.

The "JobDay" values in Tracking ("A") will always be grouped together; all the ones in sequential rows, all the twos, etc. The actual range of ones, twos, etc will vary from week to week.

Here's the code I wrote by pulling bits and pieces from other macros and Google before I realized I had no idea how to make this work.

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim JobDay As String, oPut As Long
    Dim lRowA As Long, i As Long
    Dim wsB As Worksheet

'   Select the Data worksheet
    Set wsB = ActiveWorkbook.Sheets("Tracking")
    wsB.Select

    With wsB
'   Find the last Row
    lRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

'    Collect value based on JobDay #
        For i = lRowA
               JobDay = .Range("A" & i).Value

                If JobDay = 1 Then
                    Range("M").Value + Sheets("Summary").Range("G4") = oPut
                    oPut = Sheets("Summary").Range("G4").Value
        Next i
    End With
    
    
End Sub

I hope I've explained this clearly enough that it makes sense.

Thanks in advance for your attention and time,
~ Phil

 

[Eric W]

You realize you can do that with just a formula?

In G4 on your Summary sheet put:

=SUMIF(Tracking!$A:$A,COLUMNS($G:G),Tracking!$M:$M)

then drag it to K4.

If you want to do it via VBA, you can do something like this:

    For i = 1 To 5
        Sheets("Summary").Cells(4, 6 + i).Value = WorksheetFunction.SumIf(Sheets("Tracking").Range("A:A"), i, Sheets("Tracking").Range("M:M"))
    Next i

which you can see is pretty much the same thing.

 

[Philip1957]

Eric,

Thank you very much. I did not realize I could do this with a formula! So much easier.

Thanks again,
~ Phil

 

[Eric W]

Excel is so big and has so many features, it's impossible to know them all! I'm glad this worked for you.