Append Data From One Workbook to Another Workbook (Consolidate Random Columns)

I need to take information from several data workbooks that all have the same header row but the data in row2 onwards will vary and append to a particular sheet in a master workbook. Additionally the column lengths will vary for each data workbook. I need to have this information from each workbook always begin on the next blank row of the master data workbook which DOES not have the exact same header but rather just some of the columns from the data workbooks.

The data workbooks are formatted with this macro:

Please Login or Register  to view this content.

I have seen this thread that appears to do what I am wanting but it pertains to having the same header rows.

Additionally this template seems to address what I am looking for, but I am unsure as to what I need to modify to make it work for my needs. I would like to be able to add both together as one macro if at all possible.

Hello capnhud,

Your question is a common one and easy to answer when the needed information is available.You should post a master workbook and one or more of the source workbooks. In the master there should be worksheet showing how the data should be arranged.

When i try to put these two macros together I get the following error

Compile error:
Variable not defined

without trying to add the append macro the first part of the macro works correctly, but once I try to put them together is when I get the error:

Please Login or Register  to view this content.

I have both the Book.xls and master.xls (contained in the zip) open and it is from the master.xls that I am trying to call the macro, I believe that since the beginning part of the macro acts first in the non activated workbook that is why I am having problems. Because now what I am telling the macro to do is reformat the open workbook and then bring the data to the workbook I have activated. Not sure how to alleviate that problem.


What I am trying to do is open a workbook (Book.xls) and another workbook (master.xls) and in the master workbook I am trying to say I want book.xls reformatted and the have the information to master.xls. And any other workbook that is similar to Book.xls (Video.xls) I too want that data formatted and brought to me. I hope that I am making sense.

Ok I think I addressed the previous error:

Compile error:
Variable not defined


by putting

Please Login or Register  to view this content.

but now I am get this error:

Run-time error '91'
Object variable or With block variable not set

Please Login or Register  to view this content.

Ok I have the header rows the same as the destination sheet and have a macro that combines all the workbooks into one workbook, but with sheet names that are taken from the workbook. Using the following code

Please Login or Register  to view this content.

I then use the following macro to format the sheets:

Please Login or Register  to view this content.

but when I try to consolidate random columns I get the following error:

Run-time error '9':
Subscript out of range

using the following macro:

Please Login or Register  to view this content.

the offending line is

Please Login or Register  to view this content.

I thought I had done everything correctly but apparently not. Attached are files that can help to troubleshoot the problem

Hello capnhud,

I don't see any attachments. Try uploading the files again.

I didn't even notice that they were not there

You're editing the wrong things and using illegal syntax. "C2:C" doesn't make a valid range.

The macro you're trying to use only requires that you put the headers you want to "match and collect data for" in the target sheet. Only put in the columns you want to collect.

Then the macro will open the target workbook and search each sheet for a column headers matching the ones in your target workbook, if a match is found the data from that column is collected, then it moves on and repeats for every sheet in the source workbook.

You do not need to try and separate out the columns you want to collect. Just list them all. If there are column headers in your target that don't match anything, they will simply be skipped.

When I try to run this from template.xls with the following code:

Please Login or Register  to view this content.

I get the following error:

Run-time error '9':
Subscript out of range


I have the source workbook closed and the template.xls open attached are files I am trying to apply this to.

The assumption was that the CONSOLIDATED REPORT was a "subset" of the source data columns, that all the column Headers in the consolidation sheet would exist in the source sheets. From your example workbook I see it is perfectly reasonable that there would be other headers in the consolidation report that aren't part of the source workbooks, columns you're going to do other things with.

As such, I've updated the code on the website to account for non-matched columns.

Sheets to 1 Sheet - Part 2 (random column layout)

It's not designed that way. Macros in the Personal Workbook need to specifically point to objects outside of the Personal.xls file.

Look in the code for ThisWorkbook and replace it with ActiveWorkbook. That might be all you need to do.

I will give that a try.

Edit that did not work, but that is ok I will stick with what does work.