Join Tables

[22strider]

Hello Friends

Could you please help me with VBA Code for joining two tables on a common column and then generate a third table. Both the tables are on a separate worksheet and the result table needs to be on a separate sheet as well. I can easily do this using Access but my requirement is to use Excel. I am using Excel 2007.
Following are the tabls showing sample data; Table1 and Table2 are the source tables having Manufacturer P/N as the common column. And Result Table is showing how the joined table should look like.
Thanks for your help
Rajesh

Excel 2007
	A	B	C	D	E
1		Table1
2	P/N	Manufacturer P/N	Manufacturer Name
3	500-123	700-ABC	ABC
4	500-123	700-DEF	DEF
5	500-546	600-XYZ	XYZ
6	500-546	600-PQR	PQR
7	400-123	700-ABC	ABC
8
9
10		Table2
11	Manufacturer P/N	Manufacturer Name	Country	Type
12	700-ABC	ABC	USA	Supplier
13	700-DEF	DEF	China	Manufact.
14	600-XYZ	XYZ	India	Supplier
15	600-PQR	PQR	USA	Designer
16
17
18
19		Result Table
20	P/N	Manufacturer P/N	Manufacturer Name	Address	Type
21	500-123	700-ABC	ABC	USA	Supplier
22	500-123	700-DEF	DEF	China	Manufact.
23	500-546	600-XYZ	XYZ	India	Supplier
24	500-546	600-PQR	PQR	USA	Designer
25	400-123	700-ABC	ABC	USA	Supplier
26
Sheet1

 

[ExcelTheCell]

And this can be placed in worksheet 1 if you want that it works on change

Private Sub Worksheet_Change(ByVal Target As Range)
'If change in first worksheet has filled all 3 fields then call macro Joiner
Lastcell = WorksheetFunction.CountA(Columns(1))
If Range("A" & Lastcell).Offset(0, 1).Value = "" Then Exit Sub
If Range("A" & Lastcell).Offset(0, 2).Value = "" Then Exit Sub
Call Joiner
End Sub

 

[RonEmm]

Rajesh,

I checked again on my side and the code works fine. It could be the names and/or ranges in your workbook. Otherwise post a sample of what goes wrong.