Hi,
Im copying rows from workbook1 to workbook2 with the code below. Now I would like to modify the code so that if there is Group name in cell B, it would sum all those customers premium who are part of that group. For example like in the picture below.
Any ideas how to modify the code?
Im copying rows from workbook1 to workbook2 with the code below. Now I would like to modify the code so that if there is Group name in cell B, it would sum all those customers premium who are part of that group. For example like in the picture below.
VBA Code:
Sub copysales()
Dim wb As New Workbook, rowToCopy As Integer
Dim lRow As Integer, nRow As Integer, rowno As Integer, colno As Integer
Set wb = Workbooks("Workbook2.xlsx")
lRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
nRow = wb.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
rowToCopy = nRow
Application.ScreenUpdating = True
For rowno = 2 To lRow
If (ThisWorkbook.Sheets("Sheet1").Range("F" & rowno) = Won Then
For colno = 3 To 5
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sheet1").Range("C" & rowToCopy).PasteSpecial xlPasteValues 'To copy premium
rowToCopy = rowToCopy + 1
End If
Next
End If
Next
End Sub
Any ideas how to modify the code?