Macro to Copy data from 2 workbooks and also find the Cell ref to a column given Value

Hello,
After a few of my VBA tries , i have hardly got a chance to deep dive inot this VBA coding and hence you can consider me a new coder

So trying something again in VBA front for which i would need some help to get the required output.

I have 2 Excel Sheet - ONe being the Role File((Keeps getting changed in terms of ADD/del data ), 1 being the Component file (Keeps getting changed in terms of ADD/del data )and the 3rd one will be my macro file ,

I am attaching the 3 files , i have managed to get the multiselct getOpenfile name , post that i need to get the data as per that entered in the macro file on TEST Sheet ,

So Basically the Macro would first need to open 2 files , then form the Macro file it will need to copy copy the Role one by one , find the DAta matching in Components Workbook and paste it under role Name , Similarly find the Col Reference to cell value entered in the Col Number

ONce the macro is run my data will look like the one pasted on "Output Sheet would look like".



Apprciate some help in this reagrd . Totally clueless how to get the above as desired above .

Pls help..



Reagrds/-
D