Loading multiple Excel files into Excel for formatting purposes

[nbuuifx]

I'm not sure if I'm posting in the right section so apologies if it is in the wrong place.

I have a scenario at work that I am trying to make much more efficient.


Currently the system is as follows:

8 people currently put data into a web based database system, the same 8 people then have to manually type the same information into a word file and print it. These 8 people are understandably getting annoyed with this.

So my job is to put things in place to reduce the workload and to speed it up.


So far I have got the system to export the data that the people have put in to 8 separate excel files (I hoped this would be enough), but unfortunately management want it in the exact same format that they are used to having it in. I can copy and paste it around to get it in the right way but this takes hours so is no better.

I have made an Excel file template of exactly how I want the data and I can use simple formulas to take the outputted excel files and put them into the right places, with the right font and colour etc.

What I would like to do, I think requires some programming?...

I would like to have 8 worksheets formatted to look correct, then a ninth worksheet with a list of the filenames of the files it should get the data from to put into the formatted sheets.


So the new procedure would be that the 8 staff members just put in their data on the web system. I would then export the 8 different files and save them on my computer. I would open a copy of my excel file and type in the 8 filenames. It would then populate the other 8 worksheets with the correct data from the other excel files. I would print these and hand them in. I see that this would take me no more than 30 minutes each time (It is done many times each year). This would save each of the 8 staff members at least an hour each time. So should save us approx. 7.5 man hours each time we have to enter data.


Could anyone help to suggest how I would do the above?

Thanks

[dflak]

I would have a master Excel file to run the process. This file contains a table with the file names to format. The code loops through the list of files in the table and then format them directly. I don't see a need to make copies and format the copies.

I would have to record macros to get the syntax for the formatting and I'd make them "functions" called by a master subroutine that passes a sheet and range to be formatted.

If the sheets are all laid out exactly the same (header columns always in the same location, number of rows can vary) then this is a relatively simple job since a lot can be "hard coded." If we have to look for where data may appear, it may get more complicated.

That's about as far as I can take this without sample workbooks. If you can provide a sample before workbook and after workbook (or even two sheets in the same workbook) and tell us things like column headers are fixed but number of rows may vary or whatever, we can provide more concrete answers.