combine workbooks stored in the same folder into 1 a summary sheet.

I have developed a skills matrix for staff at work to complete. All their results are compiled on a summary sheet which I have attached.
This all works great for use in my workplace however there are 10 branches that do the same and the area manager wants to see this summary sheet for each branch but for it to be compiled onto 1 overall summary sheet for them to view.
Each depot will mail a sheet (see attached example) to the area manager each month. I want it so that the area manager manually saves the sheet into a specific folder on their computer. All they then have to do is open the overall summary sheet to get a view of all the 10 different depots. They would do this on a monthly basis.
Here’s where I think it gets a little complicated:
- This is to be rolled out to over 30 area managers who will each set up a folder to collect all these summary sheets that are emailed to them but I cannot tell them where this folder needs to be set up so the file path to it will be unknown to me.
-The number of rows of data for each branch will vary but the columns are always exactly the same for every branch. (don’t want to lose the column formatting)
-The overall summary sheet should not contain any blank lines
-the source worksheets will always be named ‘future matrix’
- The source workbook that gets e-mailed will have an unspecified filename as its filename will be based on the branch location i.e.: Manchester / Glasgow / Edinburgh etc. etc.
-Can the overall summary sheet display the combined results continuously down the page.

I would be very grateful for your help with what is to me a really complicated request.
If my explanation doesn’t make sense I will be glad to try and clarify
Very much appreciated
Lee

You haven't really explained how the data consolidates, or what reporting will be required on the data. If all you want is all the summary data on the one sheet (ie rows 34/35), then that is one thing. But if you want to add the data, summarise it, average it etc, then this is another thing completely.

I will assume for now that you just want the data as a line entry. The approach I would take is to separate the presentation of the data from the data itself. So rather than produce a summary sheet like you have attached here, I would produce a summary data sheet. (see my sample). I would then produce a summary reporting sheet that "looks for" summary data sheets in a folder, and brings them into the summary report. (I hope that makes sense).

To do this, you would need to
search for files in the current folder
open them and check if they are in your standard summary file format
if they are, load them into the summary sheet.

Does that sound like what you need to do?

Matt

Hi Matt, thanks for your speedy reply.
The end result i want is all the summary data on one sheet.

What your suggesting sounds logical and the file you attached for the presentation of the data can be used in that format.
Every single summary sheet will look exactly the same, columns will be the same and where there is a line of entry all the data will be comlpeted. (there may be a different number of lines of data on each summary data sheet).

does this help? (sorry i am not great at explaining)

Lee

I think i have found a code in another thread which when testing it seems to work.
Is there any way of adapting this code so that when it pastes the data to the summary sheet it starts from the 5th row down? (i want the top 5 rows to include specific information about the company that i will include later, like a template/header)

thanks for any support you can give.

Please Login or Register  to view this content.