VBA Script to pull data from multiple files into a single spreadsheet

[JoHio3316]

I have created a template that all members on my team use to provide a quote to our customers. Each person on the team saves the file using a sequential name, i.e. the first document is called 000001, the next is 000002, etc. I need to pull data from those files into a master spreadsheet that tracks who was quoted, at what dollar amount, etc. For example, I will need to pull in cell A3 from document 000001 and have it populate in my master tracking form in cell A2, then pull in cell A3 from document 000002 and have it populate in my master tracking form in cell A3, etc. We currently have files 000001 through 000170. I want to create the master tracking file through VBA code because I don't want to manually populate it for all 170 documents already generated. In addition, I want to set it up so when file 000171 is created (it currently doesn't exist), the master tracking form will automatically populate with the information I need from that separate document. Is there a way to do this?

 

[XLfriend777]

Hello JoHio3316 and welcome to the forum. As nobody has responded I thought I would throw this out and see if it helps. Have you tried just using something like:

='C:\Users\xxxx\Excel\test3\[000001.xlsx]Sheet1'!$A$3

in your Master spreadsheet to reference the data needed from the other sheets?

Interestingly, if workbook 000001.xlsx is open the formula in Master reverts to:

=[000001.xlsx]Sheet1!$A$3

and it still pulls in the correct data.

Hope this helps get you started. Stay safe!