Consolidate 4 worksheets into 1

[JakeAy]

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]

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

[JakeAy]

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]

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

[JBeaucaire]

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.

Sub ConsolidateReport()
'Jerry Beaucaire   6/12/2010
'Collect data from all worksheets into custom consolidated sheet
Dim LastRow As Long
Dim NextRow As Long
Dim ws      As Worksheet
Dim wsCons  As Worksheet
Dim ColARR  As Range
Dim Clm     As Range
Dim colFIND As Range

'Application.ScreenUpdating = False

Set wsCons = Sheets("Consolidate")
wsCons.Range("A12:A" & wsCons.Rows.Count).EntireRow.Clear
Set ColARR = wsCons.Range("A11", wsCons.Cells(11, wsCons.Columns.Count).End(xlToLeft))

On Error Resume Next

For Each ws In Worksheets
    If ws.Name <> wsCons.Name Then
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        NextRow = wsCons.Range("A" & Rows.Count).End(xlUp).Row + 1
        
        For Each Clm In ColARR
            If Clm.Value <> "" Then
                
                Set colFIND = ws.Rows("11:11").Find(What:=Left(Clm, 4), _
                                     After:=ws.Cells(11, ws.Columns.Count), _
                                     LookIn:=xlValues, _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByColumns, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=False, _
                                     SearchFormat:=False)
                If Not colFIND Is Nothing Then
                    ws.Range(ws.Cells(12, colFIND.Column), _
                        ws.Cells(LastRow, colFIND.Column)).Copy _
                            wsCons.Cells(NextRow, Clm.Column)
                    Set colFIND = Nothing
                End If
            End If
        Next Clm
    End If
Next ws

Set ColARR = Nothing
Application.ScreenUpdating = True
End Sub

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

[JakeAy]

I can only say "You guys are awesome"

[JBeaucaire]

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]

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