Conditional find and copy to different worksheet.

[Peter Gasparik]

Hi, I am a new in VBA and I am wondering whether someone can help me. I would
like to create a macro which will search for string value in a worksheet
“data†column “A†and if it finds the match it will take the integer value
from worksheet “Data†column “B†and copy it to another worksheet “Namesâ€
column “B2†and additionally make sum of the values as they are always a
numbers.
A B
Peter 3
Martin 4
Albert 5
Wendy 8
Danka 77
Wendy 8
Wendy 9

Many thanks,
Peter.

 

[JLGWhiz]

Change the sheet names or index number to suit. You didn't specify where to
put the sum of the copied data so I put it in sheet2, col C, Offset 1 row
from the last entry in col B. Alter as needed.

Sub getTotal()
Dim lr As Long, lr2 As Long
Dim c As Range
'Change Sheet name or index to suit.
lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Nm = InputBox("Enter Name to Match", "NAME")
For Each c In Sheets(1).Range("A2:A" & lr)
If c.Value = Nm Then
c.Offset(0, 1).Copy Sheets(2).Range("B" & _
Sheets(2).Range("B65336").End(xlUp).Row + 1)
End If
Next
lr2 = Sheets(2).Range("B65336").End(xlUp).Row
Sheets(2).Range("C" & lr2 + 1) = _
WorksheetFunction.Sum(Sheets(2).Range("B2:B" & lr))
End Sub

 

[JLGWhiz]

This modifies the previous code to basically make the InputBox entry case
insensitive.

Sub getTotal()
Dim lr As Long, lr2 As Long
Dim c As Range
'Change Sheet name or index to suit.
lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Nm = InputBox("Enter Name to Match", "NAME")
For Each c In Sheets(1).Range("A2:A" & lr)
If LCase(c.Value) = LCase(Nm) Then
c.Offset(0, 1).Copy Sheets(2).Range("B" & _
Sheets(2).Range("B65336").End(xlUp).Row + 1)
End If
Next
lr2 = Sheets(2).Range("B65336").End(xlUp).Row
Sheets(2).Range("C" & lr2 + 1) = _
WorksheetFunction.Sum(Sheets(2).Range("B2:B" & lr))
End Sub