Copy from One excel and paste to master excel

[laravindh]

Please provide me a VBA code that will run and do the below task automatically. I would be grateful if you provide me a solution.
I am having around 100 to 150 excel files having different file names.
I mentioned the exact requirement in Steps:
​

Step:1

Sample 100 files are listed here but this names also will vary for each project.
[TABLE="class: cms_table, width: 436"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TD]247001.xls[/TD]
[TD]247021.xls[/TD]
[TD]247041.xls[/TD]
[TD]247061.xls[/TD]
[TD]247081.xls[/TD]
[/TR]
[TR]
[TD]247002.xls[/TD]
[TD]247022.xls[/TD]
[TD]247042.xls[/TD]
[TD]247062.xls[/TD]
[TD]247082.xls[/TD]
[/TR]
[TR]
[TD]247003.xls[/TD]
[TD]247023.xls[/TD]
[TD]247043.xls[/TD]
[TD]247063.xls[/TD]
[TD]247083.xls[/TD]
[/TR]
[TR]
[TD]247004.xls[/TD]
[TD]247024.xls[/TD]
[TD]247044.xls[/TD]
[TD]247064.xls[/TD]
[TD]247084.xls[/TD]
[/TR]
[TR]
[TD]247005.xls[/TD]
[TD]247025.xls[/TD]
[TD]247045.xls[/TD]
[TD]247065.xls[/TD]
[TD]247085.xls[/TD]
[/TR]
[TR]
[TD]247006.xls[/TD]
[TD]247026.xls[/TD]
[TD]247046.xls[/TD]
[TD]247066.xls[/TD]
[TD]247086.xls[/TD]
[/TR]
[TR]
[TD]247007.xls[/TD]
[TD]247027.xls[/TD]
[TD]247047.xls[/TD]
[TD]247067.xls[/TD]
[TD]247087.xls[/TD]
[/TR]
[TR]
[TD]247008.xls[/TD]
[TD]247028.xls[/TD]
[TD]247048.xls[/TD]
[TD]247068.xls[/TD]
[TD]247088.xls[/TD]
[/TR]
[TR]
[TD]247009.xls[/TD]
[TD]247029.xls[/TD]
[TD]247049.xls[/TD]
[TD]247069.xls[/TD]
[TD]247089.xls[/TD]
[/TR]
[TR]
[TD]247010.xls[/TD]
[TD]247030.xls[/TD]
[TD]247050.xls[/TD]
[TD]247070.xls[/TD]
[TD]247090.xls[/TD]
[/TR]
[TR]
[TD]247011.xls[/TD]
[TD]247031.xls[/TD]
[TD]247051.xls[/TD]
[TD]247071.xls[/TD]
[TD]247091.xls[/TD]
[/TR]
[TR]
[TD]247012.xls[/TD]
[TD]247032.xls[/TD]
[TD]247052.xls[/TD]
[TD]247072.xls[/TD]
[TD]247092.xls[/TD]
[/TR]
[TR]
[TD]247013.xls[/TD]
[TD]247033.xls[/TD]
[TD]247053.xls[/TD]
[TD]247073.xls[/TD]
[TD]247093.xls[/TD]
[/TR]
[TR]
[TD]247014.xls[/TD]
[TD]247034.xls[/TD]
[TD]247054.xls[/TD]
[TD]247074.xls[/TD]
[TD]247094.xls[/TD]
[/TR]
[TR]
[TD]247015.xls[/TD]
[TD]247035.xls[/TD]
[TD]247055.xls[/TD]
[TD]247075.xls[/TD]
[TD]247095.xls[/TD]
[/TR]
[TR]
[TD]247016.xls[/TD]
[TD]247036.xls[/TD]
[TD]247056.xls[/TD]
[TD]247076.xls[/TD]
[TD]247096.xls[/TD]
[/TR]
[TR]
[TD]247017.xls[/TD]
[TD]247037.xls[/TD]
[TD]247057.xls[/TD]
[TD]247077.xls[/TD]
[TD]247097.xls[/TD]
[/TR]
[TR]
[TD]247018.xls[/TD]
[TD]247038.xls[/TD]
[TD]247058.xls[/TD]
[TD]247078.xls[/TD]
[TD]247098.xls[/TD]
[/TR]
[TR]
[TD]247019.xls[/TD]
[TD]247039.xls[/TD]
[TD]247059.xls[/TD]
[TD]247079.xls[/TD]
[TD]247099.xls[/TD]
[/TR]
[TR]
[TD]247020.xls[/TD]
[TD]247040.xls[/TD]
[TD]247060.xls[/TD]
[TD]247080.xls[/TD]
[TD]247100.xls[/TD]
[/TR]
</tbody>[/TABLE]


All above Excel files have a common column name as highlighted in below snap. I need to copy that columns “TITLE 1, TITLE 2, TITLE 3, LOCATION” , “DRAWING” and “SHEET, NO OF TOTEL SHEET”
​

​

Step 2:

I need to paste the copied columns into another master file with name “MASTER” in the format as shown in below snaps.​

Step 3: This process continues for all the files one after the another continuously. Please refer below. But the drawing priority for the copy and paste to be followed from another excel.
Fig 1: Copy paste one after another

​

Fig :2 Priority Follow excel​

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1068333-macro-special-copy-paste.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).