Thread: Populating Master Sheet

Hi Brian,

Firstly, please make sure you wrap all code in the appropriate tags (as I have done).

Secondly, I dare say the use of merged cells is causing part of the issue (merged cells and VBA do not mix). That said, try this while on the desired consolidation tab:

Option Explicit

Sub ConsolidateSheets()
    
    Dim strConsCols As String, _
        strConsTabName As String, _
        strConsCol As String
    Dim wrtTab As Worksheet
    Dim lngRowLast As Long, _
        lngRowStart As Long
    Dim xlCalc As XlCalculation
    Dim intPasteCount As Integer
    
    strConsCols = "A:Z" 'Column(s) to be consolidated, i.e. "A:A" or "A:Z". Change to suit.
    strConsCol = "A" 'Column to consolidate data to.  Change to suit.
    lngRowStart = 4 'First data row number i.e. don't include header rows. Change to suit.
    
    If MsgBox("Is this """ & ActiveSheet.Name & """ the correct tab for the data to be consolidated to?", vbYesNo + vbQuestion, "Consolidate Sheet Tab Editor") = vbNo Then
        MsgBox "Run the macro from the desired consolidation tab.", vbInformation, "Consolidate Sheet Tab Editor"
        Exit Sub
    Else
        strConsTabName = ActiveSheet.Name
        With Sheets(strConsTabName)
            If WorksheetFunction.CountA(.Cells) > 0 Then
                lngRowLast = .Range(strConsCols).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lngRowLast >= lngRowStart Then
                    .Rows(lngRowStart & ":" & lngRowLast).EntireRow.Delete
                End If
            End If
        End With
    End If
    
     With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Please wait while the tabs are consolidated..."
    End With
    
    For Each wrtTab In ThisWorkbook.Sheets
        If wrtTab.Name <> strConsTabName Then
            If WorksheetFunction.CountA(Sheets(wrtTab.Name).Cells) > 0 Then
                lngRowLast = wrtTab.Range(strConsCols).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lngRowLast >= lngRowStart Then
                    'First paste needs to be set statically in case there's merged cells.
                    If intPasteCount = 0 Then
                        wrtTab.Range(Left(strConsCols, InStr(strConsCols, ":") - 1) & lngRowStart & ":" & Right(strConsCols, InStr(strConsCols, ":") - 1) & lngRowLast).Copy _
                            Sheets(strConsTabName).Range(strConsCol & lngRowStart)
                    Else
                        wrtTab.Range(Left(strConsCols, InStr(strConsCols, ":") - 1) & lngRowStart & ":" & Right(strConsCols, InStr(strConsCols, ":") - 1) & lngRowLast).Copy _
                            Sheets(strConsTabName).Cells(Cells(Rows.Count, strConsCol).End(xlUp).Row + 1, strConsCol)
                    End If
                    intPasteCount = intPasteCount + 1
                End If
            End If
        End If
    Next wrtTab
    
    With Application
        .Calculation = xlCalc
        .StatusBar = ""
        .ScreenUpdating = True
    End With
    
    MsgBox "The data in columns " & strConsCols & " for each sheet tab have now been consolidated into the """ & strConsTabName & """ tab.", vbInformation, "Consolidate Sheet Tab Editor"
    
End Sub

Regards,

Robert

Hi Brian,

There are two ways to exclude the tab from being consolidated. One is by excluding the tab based on its name, which will work as long as the name of the tab doesn't change. To do this, change this line of code...

If wrtTab.Name <> strConsTabName Then

...to this:

 If wrtTab.Name <> strConsTabName And _
            wrtTab.Name <> "DV" Then

The other method is to exclude the index number (as Roger Govier's clever code does) of the relevant tab, which will still work if the tab is renamed but not if it's position in the workbook is changed. To use this method, change this line of code...

If wrtTab.Name <> strConsTabName Then

...to this:

If wrtTab.Name <> strConsTabName And _
            wrtTab.Index <> 7 Then

I've leave the decision as to which one to use to you.

Regards,

Robert