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.

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


"Peter Gasparik" wrote:

> 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.

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






"Peter Gasparik" wrote:

> 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.