Copy the UsedRange of each sheet into one sheet using VBA in Microsoft Excel

In case you want to copy the used range of each worksheet into Master sheet then you should read this article. We will use VBA code to copy the data from each worksheet & then paste in another sheet without overwriting.

The macro will add a sheet with the name Master to your workbook and will copy the cells from every sheet in your workbook in this worksheet.

The first macro does a normal copy and the second macro copies the Values. The macro’s subs use the functions below; the macro’s won’t work without the functions.

 

Following are the snapshot of data from Sheet1 & Sheet2:

 

img1

 

img2

 

We need to follow the below steps to launch VB editor:

  • Click on Developer tab
  • From Code group, select Visual Basic

 

img3

 

  • Copy the below code in the standard module

 

Sub CopyUsedRange()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
    MsgBox "The sheet Master already exist"
    Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then
        If sh.UsedRange.Count > 1 Then
            Last = LastRow(DestSh)
            sh.UsedRange.Copy DestSh.Cells(Last + 1, 1)
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

Sub CopyUsedRangeValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
    MsgBox "The sheet Master already exist"
    Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then
        If sh.UsedRange.Count > 1 Then
            Last = LastRow(DestSh)
            With sh.UsedRange
                DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
                .Columns.Count).Value = .Value
            End With
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
                        After:=sh.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
                        After:=sh.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
On Error GoTo 0
End Function

Function SheetExists(SName As String, _
                 Optional ByVal WB As Workbook) As Boolean
 On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Function

 

img4

 

img5

 

img6

 

Now, the macro code is set; we will run the macro “CopyUsedRange” & it will insert a new sheet “Master” & copy the data from each sheet.

 

img7

 

Conclusion: Copying data from multiple sheets is a manual task; however; with the above code, we can consolidate data with a single click on a macro.

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube