Consolidation of Workbooks into single workbook

[Miraun]

Hello everyone;

I'm looking to run a VBA script that will take all excel spreadsheets within a specified directory and copy a named range "Data" from each worksheet, and place it into a consolidated worksheet.

"Data" array is tentatively B2:B16. However, these files are being sent to many resources to consolidate, therefore the range can change.

Subsequent records added via Column, not by row. So worksheet's 1 data would be A2:A16, and worksheet's 2 data would be B2:A16.

Names of the worksheets are variable, but all worksheets within a given directory will need to put imported.

My VBA skill is about as developed as a 2 year old's language skills. Could anyone assist me?

[jaslake]

Hi Miruan
A couple of questions:
I've seen the term "consolidation" used to mean different things by users of VBA.

1. It has been used to mean append records
2. It has been used to mean sum records
3. What is your meaning
   
4. You say that

   "Data" array is tentatively B2:B16

   Are you referring to a range named "Data"? Is so, will the Named Range always be the same size (same number of columns)?
5. You say

   So worksheet's 1 data would be A2:A16, and worksheet's 2 data would be B2:A16.

   Is this " and worksheet's 2 data would be B2:A16" correct or is it a typo?
6. You indicate that the sheet names can be variable. Will the names be similar to each other? How many sheets are in each imported workbook?

You seem to use the phrase "Worksheets" interchangeably with "Workbooks" such as this sentence

but all worksheets within a given directory will need to put imported.

Do you mean to say

but all workBOOKS within a given directory will need to put imported.

Get back to me; I may be able to help you with this. J

[Miraun]

Thanks for getting back to me Jaslake, sorry for all the confusion in my original post.


By consolidation, I am looking to import a named array from a variable number of excel files within a specifiable folder into a single file to act as a repository for the data. I do not need the values to be linked to their original workbooks or updatable. A single, one time pull of the data is sufficient.

4. Yes, "Data" is a named range. If it is not the specified range of B2:B16, then I would perfer the file name be noted elsewhere rather than imported.

5. Typos. In the consolidated view in the master workbook after the pull of data is completed, I would expect the first workbooks' data to be found in A2:A16, and the second workbook's values in B2:B16. As another point of correction, these should be two distinct workbooks' data.

6. Unfortunately, I have no control over the naming of the sheets that are provided. However, it will always be from a single worksheet within each workbook. This worksheet will be named "Response" within each workbook.

[jaslake]

Hi Miraun
Attached are several workbooks with dummied up data that I've used to test the procedures in the MacroBook. Place all these files in the same folder and run the procedure from the MacroBook.
You'll notice you have 5 source files and 1 target file. The procedure will loop through the source files, find a sheet named "Response" with a named range of "Data" and append the contents of this named range to Target Workbook Sheet name "Response". The append will begin at range A2 and progress downward for each append.
If the named range "Data" is not "B2:B16" in any given source file, that fact is flagged for you. Source4 has been deliberately set incorrectly so you can see the flag. This function was sourced from Sourced from http://www.vbaexpress.com/kb/getarti...b_id=696#instr
In order for the procedure to run, reference to Microsoft Scripting Runtime must be set ('Tools->References-> select Microsoft Scripting Runtime).
Play with it and let me know if it's doing what you need. J

PS: I will upload the remaining files in a new post

[jaslake]

Hi Miraun
Attached are the remaining files.
J

[jaslake]

Hi Miraun
I was re-reading your post and I believe I misread it earlier. You wanted your data in columnar form, not row form. Sorry about that. I'll change the macro and repost.
J

[jaslake]

Hi Miraun
Sorry to get back to you so late. Had Grandfather duties today and a meeting tonight. I just finished up the revisions to the procedures to present your data in columnar form. I also added a sheet to the Target File to capture errors for Named Ranges that are not in sync.
Try the attached and let me know if it works and if any revisions need to be made. As before, I'm uploading additional files on the next post. Place all files in the same folder.
J

[jaslake]

Attached are the additional files.
J

[jaslake]

Hi Miraun. I reread your post yet again and I must admit, I don't know what this means

As another point of correction, these should be two distinct workbooks' data.

If what I've presented doesn't meet this requirement, please let me know. J

[Miraun]

Hi Miraun. I reread your post yet again and I must admit, I don't know what this means If what I've presented doesn't meet this requirement, please let me know. J

That comment was more meant as a clarification for interchangably using workbooks versus worksheets.

The code works perfectly, exactly how I anticipated and wanted. I wish that I could read and comprehend the code a lot better. I'm doing my best to parse through and learn from it, but it pretty far beyond my abilities.

Actually, the ImportData macro is very nicely written with awesome comments, much easier to follow than the other routines.

Thank you very much for all of your help!

[jaslake]

Glad to have been able to help. I placed comments in the code so you can read those as you step through to help you understand what's happening and why.
If you're satisfied with the results, please mark the issue solved.
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save
J