[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Combine all of your sheets data into one sheet

Published on
12,235 Points
4 Endorsements
Last Modified:
Community Pick


The problem of having multiple repositories of different but related data happens to many people; consequently, many times this can occur within the same workbook. In other words, say that we have a Workbook with dispersed data on multiple Sheets, all with the same configuration: headers on row number one; data starting from row two. Now, what if we needed to see all the data together, we would have to combine the data from all of the sheets into one single sheet, called “Target” (which we want added in the same Workbook).


Although, the above can and has been done manually countless times, this article will provide you a handy automation macro to accomplish this.

Macro Process Flow
To start, we will delete the Sheet "Target", just in case that it already exists. Then we need to count the number of Sheets that the Workbook has. After that, we will add the new Sheet, "Target", where we will put all of the data. Next we have to go through all of the sheets and identify the last row and last column with data to define the range that should be copied to the Target sheet, using lstrow2 to ensure that the newly copied data from each sheet will be put into the first empty row on the Target sheet. This code assumes that the data is in contiguous columns, that are no blank columns in the middle.
On this macro, CombineSheets(), I've set the DisplayAlerts, EnableEvents and ScreenUpdating events to False at the beginning of routine and back to True at the end. This will avoid having the user see the screen moving as well as any alerts during the process of copying the data.

Macro Code
Sub CombineSheets() 

    'This macro will copy all rows from the first sheet 
    '(including headers) 
    'and on the next sheets will copy only the data 
    '(starting on row 2) 

    Dim i As Integer 
    Dim j As Long 
    Dim SheetCnt As Integer 
    Dim lstRow1 As Long
    Dim lstRow2 As Long
    Dim lstCol As Integer 
    Dim ws1 As Worksheet 

    With Application 
        .DisplayAlerts = False 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 

    On Error Resume Next 

    'Delete the Target Sheet on the document (in case it exists) 
    'Count the number of sheets on the Workbook 
    SheetCnt = Worksheets.Count 

    'Add the Target Sheet 
    Sheets.Add after:=Worksheets(SheetCnt) 
    ActiveSheet.Name = "Target" 
    Set ws1 = Sheets("Target") 
    lstRow2 = 1 
    'Define the row where to start copying 
    '(first sheet will be row 1 to include headers) 
    j = 1 

    'Combine the sheets 
    For i = 1 To SheetCnt 

        'check what is the last column with data 
        lstCol = ActiveSheet.Cells(1, Activesheet.Columns.Count).End(xlToLeft).Column 

        'check what is the last row with data 
        lstRow1 = ActiveSheet.Cells(activesheet.rows.count, "A").End(xlUp).Row

        'Define the range to copy 
        Range("A" & j, Cells(lstRow1, lstCol)).Select 

        'Copy the data 
        ws1.Range("A" & lstRow2).PasteSpecial 
        Application.CutCopyMode = False 

        'Define the new last row on the Target sheet 
        lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1 

        'Define the row where to start copying 
        '(2nd sheet onwards will be row 2 to only get data) 
        j = 2 

    With Application 
        .DisplayAlerts = True 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 


End Sub

Open in new window

Combining disparate but like structured data is a common task that many Excel users do manually, but using a macro to automate the task makes life much easier; therefore, I hope that you have found this article and the CombineSheets() macro useful.

Administrative Comment

by:Rory Archibald
I would tend to agree it needs more meat on the bones to be an article, but here are a few comments on the code:

1. You should never use Integers as row counter variables - always use Longs (it's generally more efficient to use Longs anyway to be honest).

2. Using fixed row and column boundaries really makes this apply to pre-2007 only. This:
'check what is the last column with data 
        lstCol = ActiveSheet.Cells(1, 256).End(xlToLeft).Column 

        'check what is the last row with data 
        lstRow1 = ActiveSheet.Cells(65536, "A").End(xlUp).Row 

would be better as:

        'check what is the last column with data 
        lstCol = ActiveSheet.Cells(1, Activesheet.Columns.Count).End(xlToLeft).Column 

        'check what is the last row with data 
        lstRow1 = ActiveSheet.Cells(activesheet.rows.count, "A").End(xlUp).Row

Open in new window

LVL 33

Author Comment

Any news?!?

Featured Post

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month