Matching rows in 2 sheets and copying matching rows from sheet 1 t
 

I've got 2 sheets in the same workbook - A and B.
Sheet B is a new unvalidated list of customers, sheet A is the validated
list of customers. Each sheet has a column with customer number.

I need to go through all customer numbers in Sheet B, and for each customer
number to go to Sheet A to see if there is a match. If there is a match, then
copy the whole row of the matching number from sheet A, and paste it to the
matching row of sheet B (it should be converted to uppercase before pasting).

Can you help me with macro ? Thanks!

Matching rows in 2 sheets and copying matching rows from sheet 1 t
 

You will need to change the sheet names or index numbers if
they are not, in fact, A and B. The sheet to be copied from
should be the active sheet when the procedure runs.

Sub cpynpstUC()
Worksheets("A").Activate
Dim rng As Range
lstRw = Worksheets("B").Cells(Rows.Count, 1).End(xlUp).Row
lstCol = Worksheets("A").Cells(2, Columns.Count).End(xlToLeft).Column
Set bRng = Sheets("B").Range("$A$2:$A" & lstRw) 'Assumes Header Row
Set aRng = Sheets("A").Range("$A$2:$A" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each c In bRng
If Not c Is Nothing Then
x = c.Address
For Each i In aRng
If Not i Is Nothing Then
y = i.Address
If c.Value = i.Value Then
Range(y).EntireRow.Copy Sheets("B").Range(x)
Set convRng = Sheets("B").Range(x & ":IV" & Range(x).Row)
For Each rng In convRng
If rng.HasFormula = False Then
rng.Value = UCase(rng.Value)
End If
Next
End If
End If
Next i
End If
Next c
End Sub

"fbagirov" wrote:

I've got 2 sheets in the same workbook - A and B.
Sheet B is a new unvalidated list of customers, sheet A is the validated
list of customers. Each sheet has a column with customer number.

I need to go through all customer numbers in Sheet B, and for each customer
number to go to Sheet A to see if there is a match. If there is a match, then
copy the whole row of the matching number from sheet A, and paste it to the
matching row of sheet B (it should be converted to uppercase before pasting).

Can you help me with macro ? Thanks!