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

 

Sometimes, it becomes a routine work to copy data from multiple sheets for the same column. This step can be avoided using automation. If you want to make a consolidated sheet after copying data from a column of each and every sheet into one sheet, then you should read this article.

In this article, we will create a macro to copy data from a specific column and paste into a new sheet.

Raw data for this example consists of employee data in the form of an Excel workbook containing three sheets with the departmental, personal and contact details of employees.

ArrowRaw

To copy data from different sheets into a new sheet, we have created a macro “CopyColumns”. This macro can be run by clicking the “Run macro” button on the “Main” sheet.

ArrowMain

“CopyColumns” macro will insert a new sheet named as “Master” after the “Main” sheet. “Master” sheet will contain consolidated data from all the sheets.

ArrowOutput

Code explanation

Worksheets.Add(after:=Worksheets(“Main”))

Above code is used to insert new worksheets after the “Main” worksheet.

If Source.Name <> “Master” And Source.Name <> “Main” Then

End If

Above code is used to restrict copy of data from the “Master” and “Main” sheet.

Source.UsedRange.Copy Destination.Columns(Last)

Above code is used to copy data from the source sheet to destination sheet.

For Each Source In ThisWorkbook.Worksheets

If Source.Name = “Master” Then

MsgBox “Master sheet already exist”

Exit Sub

End If

Next

Above code is used to check whether “Master” sheet already exists in the workbook. Macro will stop execution if “Master” sheet already exists in the workbook.

 

Please follow below for the code

Option Explicit
Sub CopyColumns()

Dim Source As Worksheet
Dim Destination As Worksheet
Dim Last As Long

Application.ScreenUpdating = False

'Checking whether "Master" sheet already exists in the workbook
For Each Source In ThisWorkbook.Worksheets
    If Source.Name = "Master" Then
        MsgBox "Master sheet already exist"
        Exit Sub
    End If
Next

'Inserting new worksheets in the workbook
Set Destination = Worksheets.Add(after:=Worksheets("Main"))

'Renaming the worksheet
Destination.Name = "Master"

'Looping through the worksheets in the workbook
For Each Source In ThisWorkbook.Worksheets
    
    
    If Source.Name <> "Master" And Source.Name <> "Main" Then
        
        'Finding the last column from the destination sheet
        Last = Destination.Range("A1").SpecialCells(xlCellTypeLastCell).Column
        
        If Last = 1 Then
            'Pasting the data in the destination sheet
            Source.UsedRange.Copy Destination.Columns(Last)
        Else
            Source.UsedRange.Copy Destination.Columns(Last + 1)
        End If
    End If
Next

Columns.AutoFit

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



Example:


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

  1. Hi, I don’t know what could I possibly done wrong, but after launching your macro I only get blanc master sheet. Thanks

    • Hi Ladislav,

      The above codes consolidate data of column A from all existing sheets to new spreadsheet in column A. The reason of getting blank master sheet could be because you must be running macro with no data in column A in existing sheets hence result is showing only blank sheet.

      We recommend you to plot some data in column A of all existing sheets before running the macro. However, still you face any problem, feel free to write us back.

      Thanks,
      Site Admin

  2. Hi

    Thanks you, it’s already more than I could find on other sites.

    I have two questions though:
    1. In the column that’s need to be copied from every worksheet are formulas. Can I change something in the macro, so it only copies the values?

    2. I was wondering if it was possible to define the range based on the header in the second row of the column? Header = “Avg”
    Because of the lay-out, the column isn’t always in the same place.

    Thank you in advance

Leave a Reply

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

*

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>