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.
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.