Copy a range from each sheet into one sheet using VBA in Microsoft Excel

In this article, we will create a macro to copy data from all the sheets in the workbook to a new sheet.

Raw data for this example consists of employee details from different departments in different sheets. We want to consolidate employee details into a single sheet.


We have created “CopyRangeFromMultipleSheets” macro for the consolidation of the data. This macro can be run by clicking “Consolidate data” button.


Macro will create a new worksheet and insert the consolidated data from all the worksheets.


Code explanation

'Looping' through all sheets to check whether "Master" sheet exists.

For Each Source In ThisWorkbook.Worksheets

If Source.Name = "Master" Then

MsgBox "Master sheet already exists"

Exit Sub

End If


Above code is used to check whether “Master” sheet exists in the workbook. If “Master” sheet exists in the workbook, then code exits and an error message is displayed.


Above code is used to get the row number of last cell in the sheet.

Source.Range("A1", Range("A1").SpecialCells(xlLastCell)).Copy Destination.Range("A" & DestLastRow)

Above code is used to copy the specified range to the defined cell.


Please follow below for the code

Sub CopyRangeFromMultipleSheets()

'Declaring variables
Dim Source As Worksheet
Dim Destination As Worksheet
Dim SourceLastRow, DestLastRow As Long

Application.ScreenUpdating = False

'Looping through all sheets to check whether "Master" sheet exist
For Each Source In ThisWorkbook.Worksheets
    If Source.Name = "Master" Then
        MsgBox "Master sheet already exist"
        Exit Sub
    End If

'Inserting a new sheet after the "Main" sheet
Set Destination = Worksheets.Add(after:=Sheets("Main"))

Destination.Name = "Master"

'Looping through all the sheets in the workbook
For Each Source In ThisWorkbook.Worksheets
    'Preventing consolidation of data from "Main" and "Master" sheet
    If Source.Name <> "Main" And Source.Name <> "Master" Then
        SourceLastRow = Source.Range("A1").SpecialCells(xlLastCell).Row
        If Source.UsedRange.Count > 1 Then
            DestLastRow = Sheets("Master").Range("A1").SpecialCells(xlLastCell).Row
            If DestLastRow = 1 Then
                'copying data from the source sheet to destination sheet
                Source.Range("A1", Range("A1").SpecialCells(xlLastCell)).Copy Destination.Range("A" & DestLastRow)
                Source.Range("A2", Range("A1").SpecialCells(xlCellTypeLastCell)).Copy Destination.Range("A" & (DestLastRow + 1))
            End If
        End If
    End If


Application.ScreenUpdating = True

End Sub


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at


  1. if i have sheet name : sheet1,sheet2 until sheet12
    i want data in row m11 : m30 in sheet1 copy link to sheet2 row ("k11:k30),and copy link data from sheet2 row m11 : m30 to sheet3 row k11:k30..etc until sheet11 row m11:m30 to sheet12 row m11:m30

    note : i want keep data from sheet1 to sheet12 ,if data in sheet1 change in sheet2 until sheet12 also change (update data) i want use update link dont copy value ,..i want you help please

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.