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.
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.