Pulling data from multiple excel files

I run a report every month.
For 10 offices.

So in 4 months, I have 40 reports.

Say we have
Month 1 (10 excel files in this folder)
Month 2 (10 excel files in this folder)
Month 3 (10 excel files in this folder)
Month 4 (10 excel files in this folder)

Each of this files has the following data,
Column 1 Header : Office Name
Column 2 Header : Product Sold
Column 3 Header : Dollar value

(along with a bunch of other columns).

I want to automate this,
1. Create a new excel file, and write a script that'll look into each of the 40 files and pull Columns 1 and Columns 3 for a specific office name...

Any help appreciated.

Thanks !

Yes it is possible.

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Originally Posted by sstocki

I run a report every month.
For 10 offices.

So in 4 months, I have 40 reports.

Say we have
Month 1 (10 excel files in this folder)
Month 2 (10 excel files in this folder)
Month 3 (10 excel files in this folder)
Month 4 (10 excel files in this folder)

Thanks !

wow I see this growing into 1 huge headache! It would make more sense if you could put them all into 1 file, on different tabs?