Copy a column or columns from each sheet into one sheet using VBA in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/cells-ranges-rows-and-columns-in-vba/copy-a-column-or-columns-from-each-sheet-into-one-sheet-using-vba-in-microsoft-excel.html">
SHARE




Sometimes, it becomes a daily work to copy data from multiple sheets for the same column but this step can be avoided by using automation. If you want to make a consolidated sheet after copying a column’s data from every sheet into one sheet then you should read this article. Here we will use VBA code to copy data from a specific column & then paste into the added sheet.

 

To copy & paste data in one sheet, we need to follow the below steps to launch VB editor:

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

 

img1

 

  • Click on Insert then Module

 

img2

 

  • This will create new module
  • Enter the following code in the Module

 

Sub CopyColumn()
    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 = Lastcol(DestSh)
                sh.Columns("A:A").Copy DestSh.Columns(Last + 1)
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Sub CopyColumnValues()
    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 = Lastcol(DestSh)
                With sh.Columns("A:A")
                    DestSh.Columns(Last + 1).Resize(, _
                    .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

 

img3

 

img4

 

img5

 

  • We need to run the macro “CopyColumn” & this will call other Functions
  • The above code will copy data from column A specifically from each sheet & then paste in “Master” sheet
  • If you want to copy data from other than column A then you need to simply change the code & mention the new column name

 

Conclusion: Using above macro code, we can delete rows which do not contain the predefined text or numbers. With little bit of alteration in macro code, we can get the result.

 

image 48

 

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 [email protected]

 
 

Please follow and like us:
42


One thought on “Copy a column or columns from each sheet into one sheet using VBA in Microsoft Excel

Leave a Reply

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


three + = 5

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>