Need Macro Created for pulling info of files inside a folder

Thriftlife

New Member
Joined
Jul 24, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Well i only need certain cells tkaen from each file not the whole sheet itself
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top