summing values based on matching column across sheetsSolved/Closed

Question

Hello, 

Hello, 


I would match data across sheets based on matching columns B then should add & summing the values to sheet summary under headers BUYING and SELLING . should add the values to last columns (BUYING ,SELLING) ,because evrey month insert three columns BUYING,SELLING,NET. the most important BUYING,SELLING should match data across sheets based on columns B and put the values under headers (BUYING ,SELLING).

as in picture 1 , the values which showing in column H brings from the other file . it's openning balances , so it 's not relating of the others sheets .

sheet summary



sheet 1


sheet2


the  result in sheet summary

TrowaD · Answer

Hi Leapout,

Could you check if the code below yields the desired result:
Sub RunMe()
Dim mWS1, mWS2, mWS3 As Worksheet
Dim mRow, x, lRow As Long
Dim mFind As Range

Set mWS1 = Sheets("Sheet1")
Set mWS2 = Sheets("Sheet2")
Set mWS3 = Sheets("Summary")
x = 2
mWS1.Select
lRow = Range("B1").End(xlDown).Row

nSheet:
For mRow = x To lRow
    Set mFind = mWS3.Columns("B").Find(Cells(mRow, "B").Value)
    mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = Cells(mRow, "F").Value
    mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = Cells(mRow, "G").Value
    If mRow = lRow And ActiveSheet.Name = mWS1.Name Then
        mWS2.Select
        lRow = Range("B1").End(xlDown).Row
        GoTo nSheet
    End If
Next mRow
End Sub

Best regards,
Trowa

TrowaD · Answer

Hi Leapout,

Sure thing. The last BUYING and SELLING columns will be cleared, before the new values will be placed.

Here is the code (also with both adjustments from post 3):
Sub RunMe()
Dim mWS1, mWS2, mWS3 As Worksheet
Dim mRow, x, lRow, lCol As Long
Dim mFind As Range

Set mWS1 = Sheets("Sheet1")
Set mWS2 = Sheets("Sheet2")
Set mWS3 = Sheets("Summary")
x = 2
lCol = mWS3.Cells(1, Columns.Count).End(xlToLeft).Column
Range(mWS3.Cells(2, lCol - 2), mWS3.Cells(Rows.Count, lCol - 1)).ClearContents
mWS1.Select
lRow = Range("B1").End(xlDown).Row

nSheet:
For mRow = x To lRow
    Set mFind = mWS3.Columns("B").Find(Cells(mRow, "B").Value)
    mWS3.Cells(mFind.Row, lCol).Offset(0, -2).Value = mWS3.Cells(mFind.Row, lCol).Offset(0, -2).Value + Cells(mRow, "F").Value
    mWS3.Cells(mFind.Row, lCol).Offset(0, -1).Value = mWS3.Cells(mFind.Row, lCol).Offset(0, -1).Value + Cells(mRow, "G").Value
    If mRow = lRow And ActiveSheet.Name = mWS1.Name Then
        mWS2.Select
        lRow = Range("B1").End(xlDown).Row
        GoTo nSheet
    End If
Next mRow
mWS3.Select
End Sub

Best regards,
Trowa

Summing values based on matching column across sheets

2 responses

Similar discussions

Subscribe To Our Newsletter!