Hello Community,
my english isn't the best so i hope you'll understand me. I've 2 tables. Table 1 (My Master table) is full of names like Apollo, Citrus, Deng etc. In table 2 are also names and material numbers. Every month i'll get e new table 2 with maybe new material numbers.
So i like to copy automatically every "match" between table 1 and table 2 in a new table. My problem was to compare the names in table 1 und 2, because the names aren't identical. For exmaple table 1 name is Apollo and the name in table 2 is Appollo inc. or F.Apollo.
So i created a formular in table 1, which creates the name like *[name]*. After that i wrote a macro which filtered table 2 with my *[names]* and copy the names & numbers into the new table.
Now i would like to copy the real name from table 1 next to my results. But here is my problem. I count the results and copy the real name into Range c, but every real name override the one before.
I hope you understand my problem and one of you can give me an advice.
Thanks for reading my post.
my english isn't the best so i hope you'll understand me. I've 2 tables. Table 1 (My Master table) is full of names like Apollo, Citrus, Deng etc. In table 2 are also names and material numbers. Every month i'll get e new table 2 with maybe new material numbers.
So i like to copy automatically every "match" between table 1 and table 2 in a new table. My problem was to compare the names in table 1 und 2, because the names aren't identical. For exmaple table 1 name is Apollo and the name in table 2 is Appollo inc. or F.Apollo.
So i created a formular in table 1, which creates the name like *[name]*. After that i wrote a macro which filtered table 2 with my *[names]* and copy the names & numbers into the new table.
Now i would like to copy the real name from table 1 next to my results. But here is my problem. I count the results and copy the real name into Range c, but every real name override the one before.
Code:
Sub Match()
Dim iRow As Long
For i = 1 To ThisWorkbook.Sheets("Customer").UsedRange.Rows.Count
ThisWorkbook.Sheets("Table2").Range("$A$1:$B$118862").AutoFilter Field:=2, Criteria1:=ThisWorkbook.Sheets("Customer").Range("C" & i)
ThisWorkbook.Sheets("Table2").UsedRange.Offset(1).Copy
ThisWorkbook.Sheets("new_table").Cells(Sheets("new_table").Range("A65536").End(xlUp).Offset(1, 0).Row, 1) _
.PasteSpecial Paste:=xlPasteValues
For j = 1 To ThisWorkbook.Sheets("Tabelle2").Cells(Rows.Count, "A").End(xlUp).Row - 1
ThisWorkbook.Sheets("table1").Range("A" & i).Copy
ThisWorkbook.Sheets("new_table").Cells(j, 3).Offset(1).PasteSpecial Paste:=xlPasteValues
Next j
ThisWorkbook.Sheets("table2").ShowAllData
Next i
End SubI hope you understand my problem and one of you can give me an advice.
Thanks for reading my post.
