Macro - Folder loop to gather info?

[Vihral]

Hi all,

I have about 25 named folders (Agent 1, agent 2, agent 3) etc. and in each folder there is an .xlsx file named just as the folder (folder: Agent 1, XLSX File: Agent 1.xlsx etc.).

Then in the Summary file (Structure is 25 folders inside a folder called "Silent Coaching" and in the Folder Silent coaching the summary file (Silent Summary) is located). In the Silent Summary.xlsx I have a Macro that looks like this (Sorry for the wall of text. If there is a way to short this down please let me know! ):

Please Login or Register  to view this content.

This fills out the information I need from the 12 different sheets from my other workbook.

Now to what I need to do (NO idea how to do this.) is I want this when the information is gathered from the first file to go to the next folder and open Agent 2.xlsx and so on all the way until the last folder (this can be 20 or 32, no exact number.). I then want this to grab the information But since there are a lot of agents (25 people, all named differently (it wont be Agent 1, agent 2 etc. it will be John Doe, Jane Doe, Martha Stewart etc) I want it to check A1, A6, A11 (Where the agents name will be (for example, in cell A1 there will say "Agent 1" since its his information I want pasted there)) and then post the correct information into the correct cell.

The example has the agents name in A1, then in A2, A3 and A4 the criteria (Date, Score, Case) and then to the right the pasted information. Same will apply for next agent (agent 2 will be in A6 etc. etc.). Would like it to match the name of the XLSX file it opens to paste in the correct spot if possible?

It sounds VERY tough from where I am looking but im hoping someone here understands what I want and can help me!

Thanks in advance for any help anyone here can provide!

[ragulduy]

Please Login or Register  to view this content.

is one way of looping through files

[Vihral]

Please Login or Register  to view this content.

is one way of looping through files

How would this apply to my code to make it work? And would it be able to handle Different names (John doe, Jane doe) and still paste them into the correct spot in the file or do I have to manually type in EACH agent?



Ill try to give you the structure here but its hard!

Silent coaching
-- John doe
----- John Doe.xlsx
-- Jane Doe
-----Jane Doe.xlsx
-- Marta Raw
----- Marta Raw.xlsx
-- Rudolf Raindeer
----- Rudolf Raindeer.xlsx
-- Santa Claus
----- Santa Claus.xlsx
-- Sad Santa
----- Sad Santa.xlsx



In the Silent summary file (Located directly in Silent Coaching) I have a sheet called Slask.
In this sheet I have the agents name in A1, A6, A11, A16, A21, A26 etc.

I want the file to go into --John Doe and open John Doe.xlsx. Copy the information I need (This is the code I posted earlier) and paste into B2, B3 and B4 to M2, M3 and M4 (Just like it does now). I then want it to close John Doe.xlsx and go to the folder called Jane Doe and open Jane Doe.xlsx and then copy all these files into B7, B8 and B9 to M7, M8 and M9. I want it to do this automatically.

And lets say that a New Agent comes in which goes into the second place in the folder structure (based on names) (New agent named Hey Hey). I want it to automatically put Hey Heys information all the way down to where he is in the SLASK sheet.

God I hope this makes sense. If not ill try to get a sample file later but itll take some time

[ragulduy]

To give you an example, this would put A1 from each individual workbooks sheet1 in column A of a workbook called "summary.xls"

Please Login or Register  to view this content.