Combine Data from Multiple Worksheets into a Single in Excel VBA

This tutorial will explain how to summarize or combine data from multiple worksheets into a single, in the same workbook.

Suppose that we are working on a workbook that contains multiple worksheets.

Where we have one worksheet as “Summary”, and we want to consolidate the rest of the worksheet data into this worksheet.

First of all, we must understand our data set, and what how it looks like.

Combine Data from Multiple Worksheets into a Single_Image_01

So here is our workbook, which contains multiple worksheets.

As you can see that there are five (5) worksheets in this workbook.

And here in this tutorial, with the help of VBA macro, we will combine all worksheet data into a master worksheet, which is the “Summary” sheet in this case.

Please note that “Summary” sheets already have some pre-existing datasets.

So when we will copy the data from the rest of the worksheet, it must be copied below the existing dataset.

Here we have four (4) worksheets “East”, “West”, “North” and “South”, which also contains some dataset.

Here we will create a VBA macro to combine all worksheet data into a “Summary” sheet.

Combine Data from Multiple Worksheets into a Single

Go to the Developer tab and then click on the Visual Basic tab, on the very left side of the ribbon.

Once you click on it you will get a new VBA editor window, see the image below:-

In this VBA editor window, go to the ‘Insert’ tab and click on the ‘Module’ options, listed under this tab.

Once you click on it you will get a new VBA module, where we will write a VBA Macro. See the image below:-

See that in the module we have written some VBA code, which needs to understand step by step.

Consolidate_Worksheets – our macro name

And below we have decided on some variables which we will use in this VBA macro

L_Row – Last row of the worksheets data (East, West, North, and South)

L_Column – Last column of the worksheets data (East, West, North, and South)

LR_Summ – Last row of the summary worksheets

sht – is a variable for worksheets

wbk – is a variable for the workbook

Here we have set the variables which we are going to use in this VBA macro.

So here we need to copy data from each of the sheets first.

To copy the data from each sheet, we will use ‘For Each Loop’, in this case.

Because For Each Loop enters into each of the sheets and also handles the rest of the VBA command.

See the below image and try to understand the VBA code:-

What this above VBA code is saying?

This code is saying if the worksheet’s name does not equal “Summary” then select it.

The VBA code will not select the “Summary” sheet because we do not need to copy data from this sheet, here we will paste the data from the rest of the sheets.

This means this will select the rest of the sheets one by one and follow our VBA command within each sheet.

So when this VBA code will enter into the first sheet, which is “East” here.

See how this sheets data lookalike:-

Note some key points here in this image.

Last Row – last row of the dataset

Last Column – last column of the dataset

Why do we need to define the last row and last column?

In automation, everything will be executed by the code.

So we do not know where our dataset is existing.

So we need to give some instruction to the VBA command, which will find the last row and last column.

These two things are here very important, they will decide our dataset range.

This means our dataset does not exist beyond, what you can see in the above image.

So when our VBA macro enter into the first worksheet “East”, its first job is to find the last row and the last column to find the dataset range.

Once it will find the Last row and last column the next command is to copy the dataset within this range.

See in the below image how we have set the code to find the last row and last column.

Once both of the lines are found, then it will copy the entire range.

To copy the range, we will use the below line of code:-

See that this VBA code will copy our dataset from the “East” worksheet.

Here in the VBA code, we have used the range from cells (2, 1) and up to the last row and last column.

Cells (1, 1) – row number 1 and column number 1

Here our row number 1 has headings so we will not copy it, in the “Summary” sheet our dataset has already heading. So we will not copy it from the rest of the worksheets.

This is the reason to copy data from row number (2).

Once the dataset is copied then where to paste it?

The next line of code will find its destination, where we will paste it.

And once the “Summary” sheet is selected, then see what needs to be done here, in this sheet.

You remember that in this “Summary” sheet, there is some pre-existing dataset.

So if we will paste the copied dataset into the pre-existing dataset then our entire dataset will corrupt.

We need to find here the exact location where to paste the copied dataset.

So exact location is the next blank row of the existing dataset.

But how to find the next blank row of the existing dataset in the “Summary” sheet?

The below VBA code will help to find it for you.

See the image below:-

Use this code to find the next blank line of the existing dataset.

This code will find the last row of the existing dataset and see that we have added (+1) at the end of the code. Which will find the next blank row of the existing dataset.

When you will execute this line of code, it will go to the next blank row of the existing dataset.

And once it will find the next blank row of the dataset, the next line of VBA code will paste our copied data here.

Once the data is pasted into the “Summary” sheet, then our ‘For Each Loop’ will select the next worksheets named ‘West’ and follow the same procedure.

Like to find the last row and last column and then again copy the entire range here.

Then again select the “Summary” and again find the next blank line below the existing dataset and paste the copied dataset here.

This process of running the VBA code will run until all sheet data is not copied and pasted into the ‘Summary’ sheet.

So this is the VBA code, which I have explained here step by step.

See below the complete line of VBA code, we have used in this tutorial.

Sub Consolidate_Worksheets()

Dim L_Row As Long
‘variable to find the last_row for sheets data

Dim L_Column As Long
‘variable to find the last_column for sheets data

Dim LR_Summ As Long
‘variable to find the last_row for consolidated sheets data

Dim sht As Worksheet
‘variable ‘sht’ to declare sheets

Dim wbk As Workbook
‘variable ‘wbk’ to declare a workbook

Set wbk = ActiveWorkbook

For Each sht In wbk.Worksheets
    If sht.Name <> “Summary” Then
        sht.Select

        L_Row = ActiveSheet.Range(“A1048576”).End(xlUp).Row
        ‘This line of code will find the last row of the dataset

        L_Column = ActiveSheet.Range(“XFD1”).End(xlToLeft).Column
        ‘This line of code will find the last column of the dataset

        ActiveSheet.Range(Cells(2, 1), Cells(L_Row, L_Column)).Copy
        ‘This line of code will copy the entire range of the dataset

        Sheets(“Summary”).Select
        ‘This line of code will activate the “Summary” sheet

        LR_Summ = ActiveSheet.Range(“A1048576”).End(xlUp).Row + 1
        ‘This line of code will find the next blank row from
        ‘the last row of the existing dataset

        Sheets(“Summary”).Range(“A” & LR_Summ).PasteSpecial (xlPasteAll)
        ‘This line of code will paste the copied data

        End If
        Next sht
End Sub

Combine Data from Multiple Worksheets into a Single

If you want to learn more about this, please visit Microsoft Office Support.

So I hope you find this tutorial useful.

Please feel free to put your comments or suggestion in the comment box below.

Combine Data from Multiple Worksheets into a Single

Leave a Comment Cancel Reply