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.

ArrowRawData

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

ArrowMain

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

ArrowOutput

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.

Source.Range("A1").SpecialCells(xlLastCell).Row

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
Next

'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
        
        Source.Activate
        
        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)
            Else
                Source.Range("A2", Range("A1").SpecialCells(xlCellTypeLastCell)).Copy Destination.Range("A" & (DestLastRow + 1))
            End If
            
        End If
    End If
Next

Destination.Activate

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 info@exceltip.com

wan
August 28, 2014 at 7:59 pm

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 .....next in sheet2 until sheet12 also change (update data) i want use update link dont copy value ,..i want you help please

Reply ↓