PDA

View Full Version : Copy Data from Certain Cells in Multiple Workbooks to One Master Worksheet



kimbz
04-23-2012, 01:17 PM
Hello! I have been searching desperately for a solution to a project I am working on. There is a wealth of information out there, but I can’t seem to find one solution that pulls all the pieces together that I need.

Anyway…I sure hope you can help with this. First, let me preface this by saying that I work in Excel quite a bit, but with the exception of recording a few simple macros, I have very little experience in macros and even less experience with VB coding, so I am probably going to need the “For Dummies” explanation.
I have close to 100 folders within 3 or 4 different directories. In each of the folders are several individual worksheets. The worksheet itself is identical in every instance. Only the data entered into the various fill-in cells will vary. What’s a little tricky though is that the worksheet is designed to function and look more like a form (to make it easier for the user), so the data is not arranged straight across in one row. There is also at least one blank version of this form/worksheet and a Word document that gives them instructions within the same folder that the completed worksheets are in. From the little bit of checking that I have done so far I know that they are all saved with a variety of names.

What I need to do is copy the data (VALUES only) from specific cells on each of those worksheets into a master worksheet that's just a traditional looking worksheet – one row of data in the appropriate columns on the master for each completed worksheet. There are very few cells/ranges actually involved in this. From the source worksheet it would be 1) the cell for the staff member’s name (C4 – actually it may read as C4:E4 because it’s 3 merged cells in order to provide more room); 2) the budget # (A7:E7); 3) the company name (C9 – again may actually be C9:G9 due to merging); and 4) the total $ amount (G51).

The summary worksheet, which for now resides in a different path/directory, is also very simple. I will attach the summary worksheet that I started to test this (the data came from two of the forms/workbooks in this particular teacher’s folder):


The budget number comes from several cells, but because they are all in one range they copy nicely into the master worksheets designations.
I was successful in recording two different macros that worked– one that copies the data in from a single sheet, and one that copies it in from multiple sheets. As you can imagine, since I used the recording button, the one for multiple sheets took it from a half sheet of coding to 3 pages. I did it as a recording hoping I could look at the actual macro and figure out how to make it work for all the worksheets in all the folders – but it’s just beyond my knowledge of Excel.

Ideally, this is what I would like to have a macro or some other Excel wizardry do:

Open the required folders
Open each Excel worksheet/form in that folder
Copy the data (values) from each worksheet/form as specified above to the master workbook – data from each worksheet/form would go to the next empty row
Close the worksheets
Close the folder

If it makes is easier, there a couple things I could do. I could copy all of the folders into one directory. I could also or instead copy all the workbooks into one folder. I guess I’m open to suggestion and whatever would make it easiest to write the necessary coding. I think I also saw a coding option somewhere that would allow for browsing for the folder(s) to process via a dialog box prompt. That might make this more available to other potential users.

So, there it all is. I hope I haven’t overwhelmed you (I know I am). If you need further clarification please let me know.

Thank you very much in advance for any assistance you might be able to provide.