How to Copy data from Multiple Sheets to a Single Sheet in Excel using VBA

I am sharing an interesting VBA code here. Let us assume, you have data scattered across multiple sheets in your Excel workbook, and you want to show all the data in one sheet. How would you do that? I’ll show you how to copy all the data from multiple sheets to a single sheet using a simple VBA macro.

Copy data from Multiple Sheets to a Single Sheet using VBA

I have monthly sales data (for example) in different sheets, say in "Sheet2" and "Sheet3". In Sheet1, I have a column named Region that has few regions. I wish to copy all monthly sales figures from Sheet2 and Sheet3 and show the figures next to each region. See the above image.

Check out this post also... How to read Data from a closed Excel file or Workbook without actually Opening it.

The VBA Code

Here's how it is done.

Option Explicit

Private Sub Workbook_Open()
    Call copyAllSheetsToSheet1
End Sub

Sub copyAllSheetsToSheet1()
On Error GoTo ErrHandler

    Dim myWs As Worksheet

    ' Read all the sheets in this WorkBook.
    For Each myWs In ThisWorkbook.Sheets
        ' Ignore Sheet 1, since data is in sheets 2 and 3.
        If myWs.Name <> "Sheet1" Then
            If myWs.Name = "Sheet2" Then
                ' Read data range from source and copy it to its destination.
                Sheets(myWs.Name).Range("A1:B10").Copy Destination:=Sheets("Sheet1").Range("B1")
            Else
                Sheets(myWs.Name).Range("A1:B10").Copy Destination:=Sheets("Sheet1").Range("D1")
            End If
        End If
    Next
ErrHandler:
    Debug.Print Err.Description
End Sub

The above code will run when you open the Excel file, since I have written the code inside the Workbook_Open() event.

Now, every time you update the figures in Sheet2 and Sheet3, it will automatically reflect the changes in "Sheet1".

Call the Procedure using a Button

You can call the above procedure by clicking a button. Simply, add a button control in one of your active worksheet, and call the procedure from inside the button's click event like this.

Private Sub CommandButton1_Click()
    Call copyAllSheetsToSheet1
End Sub

That’s it. Simple and yet useful code. Now you can quickly and efficiently copy data from multiple sheets to a single sheet. Remember, the data structure is important. Since, you are trying to extract data based on specific named range. If you don't know what a named range means in Excel, read this post.

← PreviousNext →