Bets way to consolidate multiple sheets containing raw data

[Monty85]

As the title hints, I am looking for a solution to merge the data contained in multiple sheets into a single sheet. The data itself is formatted identically in every sheet but isn't converted to a range or table.

I've tried using the "Consolidate" function in the Data menu but that would only sum the amounts together and not stack the data down the page.

Through some searches I found the solution using Power Query but as my data isn't formatted as a table by default thats not really an option (as if i need to go into every sheet to do that I may as well just copy and paste the data).

 

[alansidman]

If you use this method, you can combine sheets that are not formatted as tables. PQ will do the formatting.

 

[Trebor76]

Hi Monty85,

What are columns that need to be consolidated?

 

[Trebor76]

Here's a possible VBA solution:

Option Explicit
Sub Macro1()

    Dim wsConsol As Worksheet, ws As Worksheet
    Dim strConsolCols As String, strColFrom As String, strColTo As String
    Dim i As Long, j As Long
    
    Application.ScreenUpdating = False
    
    Set wsConsol = ThisWorkbook.Sheets("Sheet1") '<-Sheet name for the data to be consolidated in to. Change to suit.
    strConsolCols = "A:E" '<-Columns to be consolidated. Change to suit.
    
    strColFrom = Split(strConsolCols, ":")(0): strColTo = Split(strConsolCols, ":")(1)
    
    On Error Resume Next
        i = wsConsol.Range(strColFrom & ":" & strColTo).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    If i >= 2 Then
        If MsgBox("Do you want to clear the existing consolidated data?", vbQuestion + vbYesNo) = vbYes Then
            wsConsol.Rows("2:" & i).ClearContents
        End If
    End If
    i = 0
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsConsol.Name Then
            On Error Resume Next
                i = ws.Range(strColFrom & ":" & strColTo).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If i >= 2 Then
                    j = wsConsol.Range(strColFrom & ":" & strColTo).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    j = IIf(j = 0, 2, j + 1)
                End If
            On Error GoTo 0
            ws.Range(strColFrom & "2:" & strColTo & i).Copy Destination:=wsConsol.Range(strColFrom & j)
            i = 0: j = 0
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

In my humble opinion having multiple sheets with same layout is not the wat to go. Have a single tab with all the data and working off that is far more efficient.

Regards,

Robert

 

[Monty85]

Hi Monty85,

What are columns that need to be consolidated?

Each sheet is populated in Columns B - I down to Row 41. There is also a header row in each sheet (if that gets copied across thats ok - i just need all the data in one sheet so i can create lookups into it.

 

[Monty85]

In my humble opinion having multiple sheets with same layout is not the wat to go. Have a single tab with all the data and working off that is far more efficient.

Regards,

Robert

It's not something i've set up - its just the data thats been presented to me. I have about 15 files all with about 20 sheets in each.

 

[Trebor76]

Each sheet is populated in Columns B - I

So just change this line of my code...

strConsolCols = "A:E"

...to this:

strConsolCols = "B:I"

The consolidation sheet should already have the headings in as the code only copies from Row 2 of each of the other tabs.

 

[alansidman]

Have you looked at the tutorial I supplied? Any question? Any Issues?

 

[Monty85]

If you use this method, you can combine sheets that are not formatted as tables. PQ will do the formatting.

Thanks, i think this works ok. Still requires a bit of manipulation but it does work.

Using this method could I also select multiple files to do the same thing? i.e. I have 12 files, each with 20 or so sheets within them that contain the same data that I need consolidated into one page.

 

[alansidman]

Are you saying you want to consolidate 12 files with 20 sheets into one sheet? Am I understanding you correctly?