Automatic update of master workbook with changing target workbooks

Hi Folks

I have created a workbook that monitors performance for an individual against pre-set objectives.

What I now want to do is create a manager's workbook that collates the information for each team member and works out the team average.

I know how to do this by manually linking spreadsheets however the business I'm in can have anywhere between 4 and 15 members in a team, and these members can change and move around.

I would like a VBA script that can search for the workbooks that contain the data, take a specific sheet from each workbook and populate a sheet in the manager's document.

If possible it would be able to add a sheet when a new member is added, remove a sheet when a member leaves and rename each sheet to the staff members name (this is captured on their individual workbooks)

I have found several examples of looping through a directory to find other workbooks but normally they are single sheet documents. From the description on the site, this is the nearest script to what I want that I have found, but it doesn't seem quite right for what I want. (Although I'm new to VBA so I'm not 100% sure)

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\Path" ' change to suit
Set wbDst = ThisWorkbook
strFilename = Dir(MyPath & "\*.xls", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
ActiveSheet.Name = strFilename

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I have uploaded a blank version of what I want the manager workbook to look like, the numbered tabs should update themselves to the different staff names, and the first page should average each area for each month.
The 2017 121 document is what each staff member would be filling in and the Adam 121 workbook is what it will look like as it is used.

I want the manager workbook to take the '121 Data' sheet from each staff workbook in their particular team folder.

Thanks in advance

I get that the Manager Practice sheet is where you want to collect the data.

When I look at the person's sheet. It appears that there are two possible source sheets fro this data: 121 Data and Rating. Which do you want to feed into the manager's sheets?

Also I can name the tabs with the people's names instead of numerically. How will these files be named? Jane Jones xxxxx.xlsx or Jones, Jane xxxxxx.xlsx - if I can, I would like to present the names alphabetically.

1. I'll give you a cell where you can enter in where the team folders are kept.

2. Explain to me what you mean by "I want to utilise the first few folders and add a second one in each OTM (manager) folder called EO_121DATA." I'm not quite sure what you mean by that. The way I am reading it now is that Karen's manager's report will be in S:/Karen's Area/KABBOTT and her team members will be in a sub folder called EO_121Data. Is this correct? This seems to conflict with the current scheme being: S:/OM Area/OTM Name/EO_STATS/*****.xlsx.

3. The number of people in a sub-folder doesn't matter and if a person changes a team, simply move them from one folder to the other.

4. If I read the first question correctly, you want to use the value in cell B2 on the 121 Data sheet to name the workbook sheet. This can be done.

5. If I'm reading the second question correctly, I think I can accommodate you. You tell it where the new team folder is and the program will find it.

Attached is a picture of what I roughed out so far. I envisioned that the managers have their spreadsheets in this folder. The master folder is the "root directory" for the enterprise. The manager's folder is where the spreadsheets for the people reporting to the manager are kept. I can change this to add an additional level:
- Master folder = S:/OM Area
- Manager folder = OTM Name (sub-folder to Master folder)
- Team Folder = EO_Stats (sub-folder to Manager Folder where the individual folders are kept)

So the directory structure for Karen would be: S:\Karen's Areas\KABBOTT\EO_121DATA - I think this is what you want.

One thing I'll have to work out is to alphabetize the tabs based on the names. Are the names stored as first last or last, first?

Attached is a spreadsheet that I think will do what you want. Fill in the folder names on the Control Panel Sheet. I think you want:
- Master folder = S:/Karen's Area
- Manager folder = KABBOTT
- Team Folder = EO_Stats

You will notice that there are two tabs called Start and End. These are used as "markers" for determining which sheets are personnel sheets and for keeping the Team Average formulas accurate.

The team average formulas look like: =IFERROR(AVERAGE(Start:End!D6),"") - this formula takes the average of all the cell D6 for all sheets between and including start and finish. Since the start and finish tabs are blank (no data) the average formula will ignore them.

When you run the program, it deletes all sheets between start and end, goes out to the team folder and copies in the 121 Data sheet into a new sheet named for the person in cell B2 on the sheet. I noticed that the names were first last in this cell and I converted the sheet name to be last, first. The program sorts the sheet names in alphabetical order.

You can see how this scheme will keep up with re-assignments of team members.

You can hide the start and end tabs, I left them showing for the purposes of explanation. You can also hide the Sheet List tab. The Sheet List tab is used to sort the sheets.

About the only thing that can go wrong with the program is if you have two people with the same name on the same team.