Hi
I have 100 excel files from my vendor which files have the same naming eg P17_Mug, P17_Bottle, P17_Plate... From these files, I need to extract some data in one of the tabs - Products (it is the same name across all these files). These data are in the range eg cells M1:N50. For the time being, I manually copy and paste to the new spreadsheet these data to do the analysis and report to manager.
Please advise which excel functions without opening these 100+ files (indirect, match?) or using VBA to eliminate these repetitive work. If using VBA, please how to code.
The final result in the new spreadsheet is as follows:
Products is the file name
Country is the column M in these file
Quantity is the column N in these file
Products Country Quantity
Mug Canada 100
Bottle U.K. 510
Plate Germany 85
Thanks
Ruby
I have 100 excel files from my vendor which files have the same naming eg P17_Mug, P17_Bottle, P17_Plate... From these files, I need to extract some data in one of the tabs - Products (it is the same name across all these files). These data are in the range eg cells M1:N50. For the time being, I manually copy and paste to the new spreadsheet these data to do the analysis and report to manager.
Please advise which excel functions without opening these 100+ files (indirect, match?) or using VBA to eliminate these repetitive work. If using VBA, please how to code.
The final result in the new spreadsheet is as follows:
Products is the file name
Country is the column M in these file
Quantity is the column N in these file
Products Country Quantity
Mug Canada 100
Bottle U.K. 510
Plate Germany 85
Thanks
Ruby
