VBA consolidate sheets.

[jakobt]

Have 15 sheet in same workbook.
Want to consolidate entries in column A to C in each sheet into a consolidated sheet called "Consolidated sheet"

Can this be done with a VBA formula?

 

[pbornemeier]

This is one way to do it.

Option Explicit

Sub ConsolidateSheets()
    Const sTargetWorksheetName As String = "Consolidated Sheet"
    Dim lCSWriteRow As Long
    Dim wks As Worksheet
    Dim lCSInitialIndex As Long
    
    'Clear the target worksheet
    Worksheets(sTargetWorksheetName).UsedRange.EntireRow.Delete
    
    'Processing it first would cause a blank row, so
    '  save its position and move it to end
    lCSInitialIndex = Sheets(sTargetWorksheetName).Index
    Sheets(sTargetWorksheetName).Move After:=Sheets(Sheets.Count)
    
    lCSWriteRow = 1
    For Each wks In ThisWorkbook.Worksheets
        Select Case wks.Name
        Case sTargetWorksheetName
            'Do nothing
        Case Else
            With wks
                Intersect(.UsedRange, .Range("A:C")).Copy _
                    Destination:=Worksheets(sTargetWorksheetName).Cells(lCSWriteRow, 1)
            End With
        End Select
        With Worksheets(sTargetWorksheetName)
            lCSWriteRow = .UsedRange.Row + .UsedRange.Rows.Count
        End With
    Next
    
    'Restore Consolidated Sheet position
    If lCSInitialIndex <> Sheets(sTargetWorksheetName).Index Then
        Sheets(sTargetWorksheetName).Move Before:=Sheets(lCSInitialIndex)
    End If
    
End Sub