VBA code to consolidate data tabs

M

Matrix007

New Member
Joined
Nov 8, 2017
Messages
14
Hello
I need a VBA code to consolidate data from numerous tabs into one. The format of the data tabs remain the same.
See the attached example where sales from different departments need to be copied across to the consolidated tab.
Thanking you in advance for your help.
AF1QipOVDJp2nSx8WTBkshuKufz4m33NG1bM4u7o0mom3NxxJkAYS7ha_mdtB-4Kt0DxCQ


https://photos.google.com/share/AF1...?key=UWpoR195WHBVRkRnUWFSVDkyZVNMYWREQmhNOC1n
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
We need specific details. Assume all I know is what you tell me.
I never open links.

We need things like sheet names and column numbers.
 
Upvote 0
I have 5 tabs

TAB1: "ConsolidatedData" this is where the data needs to be consolidated. I will receive data from various departments in the tab2 to tab5 format. At present I am manually copy pasting. I want to automate this

[TABLE="width: 468"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Pen[/TD]
[TD="align: right"]20[/TD]
[TD] $ 10.00[/TD]
[TD] $ 200.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Pencil[/TD]
[TD="align: right"]15[/TD]
[TD] $ 5.00[/TD]
[TD] $ 75.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Rubber[/TD]
[TD="align: right"]50[/TD]
[TD] $ 2.00[/TD]
[TD] $ 100.00[/TD]
[TD]Dept2[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Note Pad[/TD]
[TD="align: right"]12[/TD]
[TD] $ 8.00[/TD]
[TD] $ 96.00[/TD]
[TD]Dept3[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Ink pen[/TD]
[TD="align: right"]4[/TD]
[TD] $ 15.00[/TD]
[TD] $ 60.00[/TD]
[TD]Dept4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Ruller[/TD]
[TD="align: right"]6[/TD]
[TD] $ 5.00[/TD]
[TD] $ 30.00[/TD]
[TD]Dept4[/TD]
[/TR]
</tbody>[/TABLE]

TAB2: "Dept1"
[TABLE="width: 468"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Pen[/TD]
[TD="align: right"]20[/TD]
[TD] $ 10.00[/TD]
[TD] $ 200.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Pencil[/TD]
[TD="align: right"]15[/TD]
[TD] $ 5.00[/TD]
[TD] $ 75.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

TAB3: "Dept2"
[TABLE="width: 468"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Rubber[/TD]
[TD="align: right"]50[/TD]
[TD] $ 2.00[/TD]
[TD] $ 100.00[/TD]
[TD]Dept2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

TAB4: "Dept3"
[TABLE="width: 468"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Note Pad[/TD]
[TD="align: right"]12[/TD]
[TD] $ 8.00[/TD]
[TD] $ 96.00[/TD]
[TD]Dept3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
TAB5: "Dept4"
[TABLE="width: 468"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Ink pen[/TD]
[TD="align: right"]4[/TD]
[TD] $ 15.00[/TD]
[TD] $ 60.00[/TD]
[TD]Dept4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Ruller[/TD]
[TD="align: right"]6[/TD]
[TD] $ 5.00[/TD]
[TD] $ 30.00[/TD]
[TD]Dept4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Matrix007,

Try this:

Code: 
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    Dim lngCounter As Long
    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    'Clear any existing consolidated data
    lngLastRow = Sheets("ConsolidatedData").Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngLastRow >= 2 Then
        Sheets("ConsolidatedData").Range("A2:E" & lngLastRow).ClearContents
    End If
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Name <> "ConsolidatedData" Then
            lngCounter = lngCounter + 1
            lngLastRow = wsMySheet.Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lngCounter = 1 Then
                wsMySheet.Range("A2:E" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("A2")
            Else
                lngPasteRow = Sheets("ConsolidatedData").Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                wsMySheet.Range("A2:E" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("A" & lngPasteRow)
            End If
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

Robert
 
Upvote 0
Thanks for the feedback and you're welcome :)

Thanks also for the thanks and like ;)
 
Upvote 0
Hi

Is there a way that the tab names "Dept1", "Dept2", "Dept3", "Dept4" etc....can be auto populated in the "ConsolidatedData" in col F?
I have just noticed that the data that I am receiving does not always have the Col F populated, hence it would be great if the tab names can be can be auto populated in the "ConsolidatedData" in col F.

Revised example below with no Col F data in the data tabs but the tab names becomes the col F of the "consolidated tab"

TAB1: "ConsolidatedData" this is where the data needs to be consolidated. I will receive data from various departments in the tab2 to tab5 format. At present I am manually copy pasting. I want to automate this

[TABLE="width: 432"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pen[/TD]
[TD]20[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pencil[/TD]
[TD]15[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$75.00[/TD]
[TD]Dept1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Rubber[/TD]
[TD]50[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD]Dept2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Note Pad[/TD]
[TD]12[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$96.00[/TD]
[TD]Dept3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ink pen[/TD]
[TD]4[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$60.00[/TD]
[TD]Dept4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ruller[/TD]
[TD]6[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD]Dept4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]TAB2: "Dept1"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pen[/TD]
[TD]20[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pencil[/TD]
[TD]15[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$75.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]TAB3: "Dept2"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rubber[/TD]
[TD]50[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]TAB4: "Dept3"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Note Pad[/TD]
[TD]12[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$96.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]TAB5: "Dept4"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Name[/TD]
[TD]Unit Sales[/TD]
[TD]Pric Per Unit[/TD]
[TD]Sales Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ink pen[/TD]
[TD]4[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$60.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ruller[/TD]
[TD]6[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 468"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hey, this works great. I have something similar im working on and my questions is,i have example 5 tabs with different names (the tab names keep changing). I want the name of that tab to appear in the consolidated tab as cell A2,A3 etc... any suggestions?
 
Upvote 0
Is there a way that the tab names "Dept1", "Dept2", "Dept3", "Dept4" etc....can be auto populated in the "ConsolidatedData" in col F?
Click to expand...

I have just changed the range references from originally code from E to F in the following which should do the job:

Code: 
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    Dim lngCounter As Long
    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    'Clear any existing consolidated data
    lngLastRow = Sheets("ConsolidatedData").Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngLastRow >= 2 Then
        Sheets("ConsolidatedData").Range("A2:F" & lngLastRow).ClearContents
    End If
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Name <> "ConsolidatedData" Then
            lngCounter = lngCounter + 1
            lngLastRow = wsMySheet.Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lngCounter = 1 Then
                wsMySheet.Range("A2:F" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("A2")
            Else
                lngPasteRow = Sheets("ConsolidatedData").Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                wsMySheet.Range("A2:F" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("A" & lngPasteRow)
            End If
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0
Hi Make it work,

Welcome to MrExcel!!

Though ideally you start a new thread for your own requirement, this should do what you need:

Code: 
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    Dim lngCounter As Long
    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    'Clear any existing consolidated data
    lngLastRow = Sheets("ConsolidatedData").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngLastRow >= 2 Then
        Sheets("ConsolidatedData").Range("A2:G" & lngLastRow).ClearContents
    End If
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Name <> "ConsolidatedData" Then
            lngCounter = lngCounter + 1
            lngLastRow = wsMySheet.Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lngCounter = 1 Then
                wsMySheet.Range("A2:F" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("B2")
                lngLastRow = Sheets("ConsolidatedData").Range("B:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Sheets("ConsolidatedData").Range("A2:A" & lngLastRow).Value = wsMySheet.Name
            Else
                lngPasteRow = Sheets("ConsolidatedData").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                wsMySheet.Range("A2:F" & lngLastRow).Copy Destination:=Sheets("ConsolidatedData").Range("B" & lngPasteRow)
                lngLastRow = Sheets("ConsolidatedData").Range("B:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Sheets("ConsolidatedData").Range("A" & lngPasteRow & ":A" & lngLastRow).Value = wsMySheet.Name
            End If
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

It will consolidate the data from columns A to F the tabs into the ConsolidatedData tab into columns B to G. Column A will used for the tab name that has had its data copied.

Hope this helps,

Robert

Regards,

Robert
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Consolidating data from multiple tabs into one tab
Replies
6
Views
253
alansidman
A
J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
147
alansidman
A
N
  • Solved
Formula to consolidate Data and remove entries with no data in that year
2
Replies
10
Views
350
Newbie73
N
S
  • Question Question
Consolidate or Similar, Only with Formula Reference?
Replies
0
Views
261
Scotster
S
R
  • Question Question
HELP! VBA
Replies
1
Views
257
jkpieterse
jkpieterse

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top