VBA to Automate looping through multiple excel files and fetching data

justme101

New Member
Joined
Nov 18, 2017
Messages
39
Hello techies,

I am required to go through multiple files in multiple sub-folders and combine the data in them to a single sheet. This will be a recurring task so it has to be dynamic. Let me explain the situation pointwise:

1. There is a Yearly folder (2021 for now, 2022 will be added soon), and under that there are monthly sub-folders (Jan, feb...).
2. Under each sub-folder, there are daily reports and all of them have the same name except towards the end, the date changes. For e.g. Daily Sales Report 08232021, Daily Sales Report 08242021...and so on for each monthly folder.
3. Each file has a pivot tab named "Sales Pivot" which has a pivot for multiple customers, for e.g. A,B,C etc. I need to open each file, go to that tab, and click on my customer name, let's say C. This will open up a new sheet with the data for customer C.
4. I need to copy this data and paste it in a new sheet.
5. This needs to be done for all these files starting from January till now, and this will be carried out in the future as well.
6. The program can ignore all files which have already been processed earlier, as running through all the files on a daily basis does not make any sense.

I have pulled out some hair doing this and I can't seem to understand how to proceed with the VBA coding (noob here obviously). Please help me out. Thank you.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,043
Office Version
  1. 2016
Platform
  1. Windows
You need to show how your daily data sheet looks like. How you want daily data to be compiled in new workbook. Without this helpers most likely would not know where to start and end up something not to your objective.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,495
Messages
5,832,024
Members
430,104
Latest member
briannnnleong

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