Working with several workbooks

**saccoia** · 01-05-2011, 05:18 PM

Hi All
I am another newbie to VBA programming and I have a problem I need a solution to.

I am working with a master/parent workbook which contains identical copies of the worksheets from all child/class workbooks that my colleagues add data to. That is the master workbook has all the worksheets from all the class workbooks combined. I use an index page to list the names of the students in each class on the class workbooks and I have a similar sheet in the master workbook but it has a complete list of all students from all classes on it.

On a regular basis I need to transfer/copy the entered data from each class workbook onto my parent/master workbook using the list on the class workbook index sheet. I need to ensure that the worksheets in both parent/master and child/other have the same/identical worksheet name before I can transfer the data so that the info matches. That is both workbooks open and the worksheets matching. Can anyone help

**JBeaucaire** · 01-05-2011, 06:49 PM

'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.

Workbooks to Sheets - Part 4

The parts of the code that need to be edited are colored to draw your attention.

Even though your class workbooks only have one sheet, the macro will still work the same, processing the one sheet it finds.

The macro currently appends data found to the bottom of the matching sheet, but you can edit this section to have it replace the existing data:

Please Login or Register  to view this content.

...becomes:

Please Login or Register  to view this content.

**saccoia** · 01-05-2011, 08:23 PM

Thanks for that. But the class workbooks are not just one worksheet. Each of the class workbooks have several sheets (one for each student). Each of the sheets are reasonably complicated where data is entered into several cells (Semester 1 to Semester 2) throughout the sheet in specific areas. The sheets all contain several formula and do a variety of calculations. The master workbook mimicks all the sheets for all class workbooks (around 20 or so). I want to be able to open the master workbook and extract te dat from each class workbook by lining up the worksheets and then transfer the data.

**JBeaucaire** · 01-05-2011, 11:38 PM

I suppose it's time to present some sample workbooks, then. Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook(s).

**saccoia** · 01-09-2011, 07:27 PM

Hi Jerry

Thanks for your time. I am having issues trying to attach files in the forum for some reason. Can't get the icon to respond in any way. I will try to upload the files at another time.

But the gist of it is this. I will generate sheets of each student with the lists of students in the cohort for the master and the class for the class workbooks respectively. My teachers will fill in the worksheets(student profiles) with appropriate data then upload the files to a common directory. I would like to be able to look up each file/workbook from a dropdown list and then automatically update the master worksheets/profiles. I would like to update each semester separately. Thanks