replace column with another based on match column between two sheets

[abdo meghari]

Hello
I need macro to match CODE column (A) between two sheets , if they are matched then I want replace column B for MAIN sheet with column C for REPORT sheet and ignore BRANDS contain empty cells for column A in REPORT sheet .

sa
	A	B
1	CODE	BRAND
2	BSJ100	BS 1200R20 G580 JAP
3	BSJ100	BS 1200R20 G580 JAP
4	BSJ100	BS 1200R20 G580 JAP
5	BSJ100	BS 1200R20 G580 JAP
6	BSJ100	BS 1200R20 G580 JAP
7	BSJ101	BS 1200R20 G580 THI
8	BSJ101	BS 1200R20 G580 THI
9	BSJ101	BS 1200R20 G580 THI
10	BSJ102	BS 1200R24 G582 JAP
11	BSJ102	BS 1200R24 G582 JAP
12	BSJ102	BS 1200R24 G582 JAP
13	BSJ102	BS 1200R24 G582 JAP
14	BSJ103	BS 1200R20 R187 JAP
15	BSJ103	BS 1200R20 R187 JAP
16	BSJ104	BS 1200R20 R187 THI
17	BSJ105	BS 1200R24 G580 JAP
18	BSJ105	BS 1200R24 G580 JAP
19	BSJ106	BS 13R22.5 R187 JAP
20	BSJ107	BS 1400R20 R180 JAP
21	BSJ107	BS 1400R20 R180 JAP
22	BSJ108	BS 1400R20 R180BZ JAP
23	BSJ109	BS 1400R20 VSJ JAP
24	BSJ109	BS 1400R20 VSJ JAP
25	BSJ109	BS 1400R20 VSJ JAP
26	BSJ110	BS 155R12C R624 INDO
27	BSJ111	BS 155R12C R623 INDO
MAIN

sa
	A	B	C
1	CODE	ITEM	BRAND
2	BSJ103	1	BS 1200R20 R187 TCF
3	BSJ104	2	BS 1200R20-18PR R187 THI
4	BSJ105	3	BS 1200R24 G580
5		4	BS 1200R20 G580 TCF
6		5	BS 1200R24 G582 JAP
7	BSJ107	6	BS 1400R20 TCF R180
8		7	BS 1400R20 TCF 20PR R180BZ JAP
9	BSJ109	8	BS 1400R20 VSJ TCF
10	BSJ110	9	BS 155 R12C R624 IND
REPORT

result should sort from small to big based on column A and keep empty cell in the last

sa
	A	B	C
1	CODE	ITEM	BRAND
2	BSJ103	1	BS 1200R20 R187 JAP
3	BSJ104	2	BS 1200R20 R187 THI
4	BSJ105	3	BS 1200R24 G580 JAP
5	BSJ107	4	BS 1400R20 R180 JAP
6	BSJ109	5	BS 1400R20 VSJ JAP
7	BSJ110	6	BS 155R12C R624 INDO
8		7	BS 1200R20 G580 TCF
9		8	BS 1200R24 G582 JAP
10		9	BS 1400R20 TCF 20PR R180BZ JAP
REPORT

 

[JoeMo]

Untested, Try this on a copy of your workbook.

Sub abdo()
Dim shMAIN As Worksheet, shREPORT As Worksheet, lRwM As Long, lRwR As Long
Dim VMain As Variant, VReport As Variant, i As Long, j As Long, ct As Long

Set shMAIN = Sheets("MAIN"): Set shREPORT = Sheets("REPORT")
lRwM = shMAIN.Cells(Rows.Count, "A").End(xlUp).Row: lRwR = shREPORT.Cells(Rows.Count, "A").End(xlUp).Row
VMain = shMAIN.Range("A2:B" & lRwM).Value: VReport = shREPORT.Range("A2:C" & lRwR)
ReDim Vout(1 To UBound(VMain, 1), 1 To 1)
For i = 1 To UBound(VMain, 1)
    For j = 1 To UBound(VReport, 1)
        If VMain(i, 1) = VReport(j, 1) Then
            ct = ct + 1
            VMain(i, 2) = VReport(j, 3)
        End If
    Next j
Next i
If ct > 0 Then
    shMAIN.Range("A2:B" & lRwM) = VMain
    shREPORT.Range("A1:C" & lRwR).Sort key1:=shREPORT.Range("A2"), order1:=xlAscending, Header:=xlYes
Else
    MsgBox "No matches found"
    Exit Sub
End If
End Sub

 

[abdo meghari]

thanks Joe.
the result just will be in REPORT sheet when replace BRANDS column for REPORT sheet should be the same thing as in MAIN sheet.
don't change BRANDS column for MAIN sheet .

 

[JoeMo]

thanks Joe.
the result just will be in REPORT sheet when replace BRANDS column for REPORT sheet should be the same thing as in MAIN sheet.
don't change BRANDS column for MAIN sheet .

Your OP says: "I need macro to match CODE column (A) between two sheets , if they are matched then I want replace column B for MAIN sheet with column C for REPORT sheet ..."
As I now understand from your reply, all changes prompted by a CODE match should be made to the REPORT sheet, not the MAIN sheet. Try the revision below:

Sub abdo()
Dim shMAIN As Worksheet, shREPORT As Worksheet, lRwM As Long, lRwR As Long
Dim VMain As Variant, VReport As Variant, i As Long, j As Long, Vout As Variant, ct As Long

Set shMAIN = Sheets("MAIN"): Set shREPORT = Sheets("REPORT")
lRwM = shMAIN.Cells(Rows.Count, "A").End(xlUp).Row: lRwR = shREPORT.Cells(Rows.Count, "A").End(xlUp).Row
VMain = shMAIN.Range("A2:B" & lRwM).Value: VReport = shREPORT.Range("A2:C" & lRwR)
For i = 1 To UBound(VMain, 1)
    For j = 1 To UBound(VReport, 1)
        If VMain(i, 1) = VReport(j, 1) Then
            ct = ct + 1
            VReport(j, 3) = VMain(i, 2)
        End If
    Next j
Next i
If ct > 0 Then
    With shREPORT.Range("A2:C" & lRwR)
        .Value = VReport
        .Sort key1:=shREPORT.Range("A2"), order1:=xlAscending, Header:=xlYes
    End With
Else
    MsgBox "No matches found"
    Exit Sub
End If
End Sub

 

[abdo meghari]

sorry about wrong details in OP .
thanks for new version .
last thing could re-autonumbering when sort data for REPORT sheet in column B .

 

[JoeMo]

sorry about wrong details in OP .
thanks for new version .
last thing could re-autonumbering when sort data for REPORT sheet in column B .

This version includes renumbering of column B items in sheet REPORT.

Sub abdo()
Dim shMAIN As Worksheet, shREPORT As Worksheet, lRwM As Long, lRwR As Long
Dim VMain As Variant, VReport As Variant, i As Long, j As Long, Vout As Variant, ct As Long

Set shMAIN = Sheets("MAIN"): Set shREPORT = Sheets("REPORT")
lRwM = shMAIN.Cells(Rows.Count, "A").End(xlUp).Row: lRwR = shREPORT.Cells(Rows.Count, "A").End(xlUp).Row
VMain = shMAIN.Range("A2:B" & lRwM).Value: VReport = shREPORT.Range("A2:C" & lRwR)
For i = 1 To UBound(VMain, 1)
    For j = 1 To UBound(VReport, 1)
        If VMain(i, 1) = VReport(j, 1) Then
            ct = ct + 1
            VReport(j, 3) = VMain(i, 2)
        End If
    Next j
Next i
If ct > 0 Then
    With shREPORT
        .Range("A2:C" & lRwR).Value = VReport
        .Range("A2:C" & lRwR).Sort key1:=shREPORT.Range("A2"), order1:=xlAscending, Header:=xlYes
        .Range("B2").Value = 1
        .Range("B2").AutoFill .Range("B2:B" & lRwR), xlFillSeries
    End With
Else
    MsgBox "No matches found"
    Exit Sub
End If
End Sub

 

[abdo meghari]

excellent !
many thanks for your solution

 

[JoeMo]

excellent !
many thanks for your solution

You are welcome - thanks for the reply.