Conditional find and copy to different worksheet.
 

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.

Conditional find and copy to different worksheet.
 

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.

Conditional find and copy to different worksheet.
 

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.