Consolidate 4 worksheets into 1

**JakeAy** · 06-09-2010, 04:11 AM

Hi guys, before you say that this has been done a million times, what I want to do seems to be different enough to warrant a new thread.

I have a workbook with 12 worksheets. 4 of the worksheets have data I'd like to consolidate into 1. Those 4 worksheets have identical column names. They also have rows of data that I don't need at the top of the worksheet (admin stuff like who created the file, etc). Those 4 worksheets will have a varying number of rows of data in them as the source worksheets are modified.

So the question is, how do I combine those 4 worksheets into another worksheet so I have a seamless list? I've already tried analysing and modifying the VBA code shown in another post, but it's just out of my grasp.

Any advice? Example file attached (source worksheets have a "PCB" suffix).

**19fortysix** · 06-12-2010, 03:35 PM

The attached workbook should accomplish what you've requested. However, the "heater pcb" worksheet is missing a column the "Inspect 1" column that the others contain.

Hope this helps,
Gary

**JBeaucaire** · 06-12-2010, 04:59 PM

1) You'll need to do some work on the labels you've used in row 11 on each sheet, including the Consolidate sheet. They need to be consistent. Either use Qty or Nr. Required, not both.

2) You don't have to grab every column, if you'd like to consolidate less information, only list the column labels you want to see collected from each sheet on the Consolidate row 11.

3) Then run this macro, it will go sheet by sheet looking for just the columns you've asked for on Consolidate and bring over just that info.

Please Login or Register  to view this content.

NOTE: on the attached sheet I have not corrected the labels, you still need to go through all those.

**JakeAy** · 06-16-2010, 02:57 AM

I can only say "You guys are awesome"

**JBeaucaire** · 06-16-2010, 04:35 AM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**JakeAy** · 06-16-2010, 05:54 AM

Done. By the way, that process to mark a thread as "solved" is not that obvious. I suggest a slight change to the layout of the forum which puts an option to mark the thread as solved with one keypress. It could be present only for the person who started the thread so it doesn't clutter things up for respondees. I think this would result in higher compliance of people marking threads as 'solved'.

jk

**JakeAy** · 06-18-2010, 02:48 AM

19fortysix, can you tell me why this line in your macro:

NextRow = Range("A" & Rows.Count - 18).End(xlUp).Row + 1

modifies the row count by 18?

I have tried modifying this constant to a few other values and it doesn't seem to affect the final result or the value of "NextRow". By the way, you've really helped me out with your code! I've learnt a lot about visual basic through analysing it.

**19fortysix** · 06-20-2010, 01:37 PM

Sorry about that! That is what I refer to as "poor housekeeping". I grabbed from another notebook that had some data hidden away at the bottom of the workbook so stuck in the -18 to get above that data.

Gary