How to extract data from 30 different files/worksbooks into a single worksheet for selected items - vlookup across multiple files

[Sohail01]

Hi, I have 30 files in the same folder (P3/2014/PPD Data), one for each month (last 30 months). Each file has approx 6000 rows of data with prices for the drugs for that month.

I need to extract the data relating to only 5 of these drugs over the 30 months (call them drug 1, drug 2, etc). The file I will pull the data into is in the same file path and folder and is called 'FOI July18'. I need the entire row of data for these drugs from the 30 monthly files wherever they occur.

rather than cut and paste all the data into a single file from all these 30 files is there a VBA that will do this and only pull out the data for the 5 drugs?

thanks,

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ltiple-spreadsheets-into-one.html#post5114741

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).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.