Matching data from one workbook to another and copying...

[snapgt]

I have two Excel files, one is my MasterWorkbook which grabs data from multiple workbooks and compiles it in a single sheet, and another file, B1, which is the data source. To date, the data in B1 has been manually copied into MasterWorkbook. Here is a sample of what MasterWorkbook looks like:

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: center"]Dealer #[/TD]
[TD="width: 64, align: center"] Name[/TD]
[TD="width: 64, align: center"] Price[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"] AAA[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"] BBB[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"] CCC[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"] 2[/TD]
[TD="align: center"] Abc[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"] DEF[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"] GHI[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"] JKL[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"] MNO[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"] PQR[/TD]
[TD="align: center"]9[/TD]
[/TR]
</tbody>[/TABLE]

Here is what B1 looks like:
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: center"]Name[/TD]
[TD="width: 64, align: center"]New Price[/TD]
[/TR]
[TR]
[TD="align: center"]AAA[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]BBB[/TD]
[TD="align: center"]110[/TD]
[/TR]
[TR]
[TD="align: center"]CCC[/TD]
[TD="align: center"]120[/TD]
[/TR]
</tbody>[/TABLE]

What I believe should happen is that the code should look at the dealer # first, if it is equal to 1, open B1, if it is equal to 2, open B2, etc. Once the appropriate data workbook is opened, the correct name needs to be found and the associated price should be copied from the data source and into the price column of the MasterWorkbook. A few things to keep in mind, the MasterWorkbook is dynamic in terms of the number of rows and the order of dealers and names. The data sources are random in terms of their order (i.e. tomorrow it might be CCC in row 1, AAA in row 2, BBB in row 3).

So, once the code is run, the final table should look like this (assuming data source B2 has identical prices as what is in the MasterWorkbook):
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: center"]Dealer #[/TD]
[TD="width: 64, align: center"]Name[/TD]
[TD="width: 64, align: center"]Price[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]110[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]120[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Abc[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]DEF[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GHI[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]JKL[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]MNO[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]PQR[/TD]
[TD="align: center"]9[/TD]
[/TR]
</tbody>[/TABLE]

I have attempted this but keep getting hung up on switching between the two workbooks once they both are opened. I also have issues pasting the information into the MasterWorkbook as I keep getting various object errors. Any suggestions on how to do this would be appreciated!

Thanks!