Need Macro Created for pulling info of files inside a folder

[Thriftlife]

Hey have a workbook called "Office Invoice.xlsx"
What i want is to open a folder i have and go through each file and pull data from certain cells and put them in on the Office invoice file.
We add 500 or more invoices in an excel sheet on that file for each year, and it auto update when they complete and invoice.
I want to be able to run they macro and set it to a specific folder to pull all the data i need off. We uses one drive so all employees has access to the blank invoice files.

The file i want to be importing that info to is "Office Invoice.xlsx" and put into sheet named 2020.
The folder i want the info pull from is "d.docs.live.net/af07ac719c882e48/2020" took out the https://
Each file in the folder has different name, but they all have the same first sheet name "Invoice".
I want to copy the following cell from that sheet "B4,I6,G4,G5,D9,H6,H10"
Then paste it into the Office Invoice file in the columns A,B,C,D,E,F,G
I want each files info pasted onto its on separate row in the Office Invoice file
If anyone can help that would be great
Thanks Will

 

[pjmorris]

No VBA needed, I would expect to do this with PowerQuery: 'New Query, 'From File', 'From Folder' and then navigate to your chosen folder. Then chose either Transform Data or Combine and Transform. You will probably be given the option to chose which sheet is imported. The interface is pretty intuitive and it will be a whole lot easier than trying to write VBA to do so.

 

[Thriftlife]

Well i only need certain cells tkaen from each file not the whole sheet itself

 

[Thriftlife]

Well i only need certain cells tkaen from each file not the whole sheet itself

and i need to still auto update when they fill out the cells when they update the information on it

 

[pjmorris]

I understand that using Powerquery might seem like a sledgehammer to crack a nut, but the effort will be worth it. If you subsequently add addition files to the folder, as soon as you refresh your summary sheet all the data will be collected and presented. True, that can probably be done in VBA, but its probably not your best solution. Powerquery is pretty competent at filtering and removing unwanted information so I'm sure you can get what you need. Can you provide a sample of your data using the XL2BB?