Help Referencing other Workbook cells

Here is what I'm trying to accomplish I appreciate any help. I haven't done VB in ages and never within Excel

Reference closed workbook in same folder
Search column A for specific value
Once I have the specific value copy and paste a certain column value from the matching row into another workbook

I can accomplish this by copy paste but rows are added often. So I figure this is a more dynamic fool proof way of not having to redo a worksheet once a month.

Again any help is appreciated. I have to do this many times over so a framework to reference would be awesome.

Can you upload a sample workbook with no sensitive data showing how the data currently is presented and how you want it to appear after the code is run- I don't think you are going to be able to reference a closed workbook however you will be able to get the required data without having the workbook display. The fact the workbook is dynamic is easy to accommodate for however you need to upload a sample of the workbook you want to search and the workbook where data is to be copied to. Be specific in your requirements giving some examples of what you want to search for and once found where the data is to be placed into in the other workbook. Giving an example will enable you to receive a better solution to your problem.
Make sure no sensitive/private data is in the workbook you upload. Do not upload screen dumps as no one wants to reconstruct a workbook based on an image.
Without some sample workbooks with examples your question is too vague to give a reasonable answer.