Map column headings with different spelling/naming then find/copy column from one sheet to another

[jwb1012]

Hello, I am in a bit of a strange predicament, where the column headings on my system A generated report do not match the column headings that I NEED on my import sheet for system B. Also, the system A generated report has additional columns that I don't need.

I have two worksheets...

• "2 - Report" - which has the system A generated report with different heading names AND additional columns.

[TABLE="width: 928"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Location[/TD]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Quantity[/TD]
[TD]Currency[/TD]
[TD]Unit Price[/TD]
[TD]Total Price[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]AAAA[/TD]
[TD]000001[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]USD[/TD]
[TD]$8.01[/TD]
[TD]$160.20[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]AAAA[/TD]
[TD]000002[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]USD[/TD]
[TD]$40.04[/TD]
[TD]$800.80[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]AAAA[/TD]
[TD]000003[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]USD[/TD]
[TD]$80.08[/TD]
[TD]$1,601.60[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]AAAA[/TD]
[TD]000004[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]USD[/TD]
[TD]$160.15[/TD]
[TD]$3,203.00[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]AAAA[/TD]
[TD]000001[/TD]
[TD]28-Mar-2017[/TD]
[TD]25[/TD]
[TD]USD[/TD]
[TD]$7.85[/TD]
[TD]$196.25[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]AAAA[/TD]
[TD]000002[/TD]
[TD]28-Mar-2017[/TD]
[TD]25[/TD]
[TD]USD[/TD]
[TD]$39.24[/TD]
[TD]$981.00[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]AAAA[/TD]
[TD]000003[/TD]
[TD]28-Mar-2017[/TD]
[TD]25[/TD]
[TD]USD[/TD]
[TD]$78.48[/TD]
[TD]$1,962.00[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]AAAA[/TD]
[TD]000004[/TD]
[TD]28-Mar-2017[/TD]
[TD]25[/TD]
[TD]USD[/TD]
[TD]$156.95[/TD]
[TD]$3,923.75[/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]

• "3 - Import" - which I need in the format shown below. THE HEADINGS** are already on this sheet, but there is no data. This will be imported into System B.

[TABLE="width: 900"]
<tbody>[TR]
[TD]Category[/TD]
[TD]ID[/TD]
[TD]TXN_Date[/TD]
[TD]TXN_Quantity[/TD]
[TD] TXN_Unit_Price [/TD]
[TD] TXN_Total_Price [/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
[TR]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[TD]<insert data>[/TD]
[/TR]
</tbody><colgroup><col span="6"></colgroup>[/TABLE]



To solve this issue, I would like to use a lookup table (shown below) to set the required headings equal to system A headings, then find the column where the system A headings are located on the "2 - Report" worksheet, and then copy that column to the "3 - Import" worksheet.

Any thoughts on how one can do this with VBA?



[TABLE="width: 544"]
<tbody>[TR]
[TD="colspan: 4"]System A Headings[/TD]
[TD="colspan: 4"]Required Headings[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Category[/TD]
[TD="colspan: 4"]Category[/TD]
[/TR]
[TR]
[TD="colspan: 4"]ID[/TD]
[TD="colspan: 4"]ID[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Date[/TD]
[TD="colspan: 4"]TXN_Date[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Quantity[/TD]
[TD="colspan: 4"]TXN_Quantity[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Unit Price[/TD]
[TD="colspan: 4"]TXN_Unit_Price[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Total Price[/TD]
[TD="colspan: 4"]TXN_Total_Price[/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]