replace item for range with item for Table based on column

[abdo meghari]

Hello
here is data in sheet1 as TABLE not range.

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	T01 185/65 R15	188
3	2	1200R20 G580 JAP	102
4	3	1200R20 R187 JAP	133
5	4	1200R24 G580 JZ	0
6	5	1300R22.5 R187 JAP	147
7	6	1400R20 R180 JAP	97
8	7	175/65R14 B25A32 THI	150
9	8	175/70R13 EP150 THI	4
10	9	175/70R14 150 E2 THI	30
11	10	185/65R14 EP150 INDO	16
12	11	185/65R15 B250 JAP	187
13	12	185/70R13 EP 150 INDO	9
14	13	195/55R16 EP300 THI	195
15	14	195/60R16 150EZ THI	140
sheet1

and here is data in sheet2 as in range , not Table

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	5	BS 13R22.5 R187 JAP	34
3	6	BS 1400R20 R180 JAP	55
4	3	BS 1200R20 R187 JAP	32
5	4	BS 1200R24 G580 JAP	12
6	7	BS 175/65R14 B25A32 THI	23
7	1	BS 185/65R15 T001 JAP	45
8	2	BS 1200R20 G580 JAP	67
9	8	BS 175/70R13 EP150 THI	67
10	9	BS 175/70R14 ZE150 THI	88
11	12	BS 185/70R13 EP150 INDO	99
12	13	BS 195/55R16 EP300 THI	80
13	14	FS 195/60R16 150EZ THI	68
14	10	BS 185/65R14 EP150 INDO	16
15	11	BS 185/65R15 B250 JAP	187
sheet2

so I want replace items in sheet2 with item in sheet1 based on column A
the result in sheet1 should be

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	BS 185/65R15 T001 JAP	188
3	2	BS 1200R20 G580 JAP	102
4	3	BS 1200R20 R187 JAP	133
5	4	BS 1200R24 G580 JAP	0
6	5	BS 13R22.5 R187 JAP	147
7	6	BS 1400R20 R180 JAP	97
8	7	BS 175/65R14 B25A32 THI	150
9	8	BS 175/70R13 EP150 THI	4
10	9	BS 175/70R14 ZE150 THI	30
11	10	BS 185/65R14 EP150 INDO	16
12	11	BS 185/65R15 B250 JAP	187
13	12	185/70R13 EP 150 INDO	9
14	13	BS 195/55R16 EP300 THI	195
15	14	FS 195/60R16 150EZ THI	140
sheet1

and update in sheet1 automatically when add new or change the items when every time run the macro

 

[Legacy 143009]

Ok, put the last piece of code anywhere you like in button click sub.

 

[abdo meghari]

perfect !
thanks very much for all of theses codes .

 

[Legacy 143009]

You're welcome

 

[Legacy 143009]

You can also put it in a standard module like this:

Sub updateItems()
    With Worksheets("Sheet1").ListObjects("Table1").Range
      For i = 2 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
            Exit For
          End If
        Next
      Next
    End With
End Sub

And call it on button click:

Private Sub Button1_Click() 'Must be your button function
  Call updateItems
End Sub