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

Users are saying about us...

    • 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

  1. 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

  2. HI,

    I have the doubt on macro i am preparing the time sheets i have the summery sheet and same thing i had done 15 people time sheets but in summery sheet serial wise employee name and id available i have to update in multiple sheets serial wise names and employee code.
    Example : Sheet no 1 i need to update the name (b2) cell copy the name from summery serial no 1 like that 15 people can you please suggest.

    • As far as I can understand, you are trying to merge sheets into one sheet. Where every sheets data is pasted below the pervious sheet data. if you are trying that, then this code will help you.

      Sub Merge_Sheets()

      Dim startRow, startCol, lastRow, lastCol As Long
      Dim headers As Range

      'Set Master sheet for consolidation
      Set mtr = Worksheets("Master")

      Set wb = ThisWorkbook
      'Get Headers
      Set headers = Application.InputBox("Select the Headers", Type:=8)

      'Copy Headers into master
      headers.Copy mtr.Range("A1")
      startRow = headers.Row + 1
      startCol = headers.Column

      Debug.Print startRow, startCol
      'loop through all sheets
      For Each ws In wb.Worksheets
      'except the master sheet from looping
      If ws.Name <> "Master" Then
      ws.Activate
      lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
      lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
      'get data from each worksheet and copy it into Master sheet
      Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
      mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
      End If
      Next ws

      Worksheets("Master").Activate

      End Sub

      soon I will upload an article, explaining this code.

  3. I'm trying to copy 4 columns in 1 sheet and paste it in another sheet. The kicker is that i have to combine those 4 columns into 1. I can write the code to copy and paste it to 4 different columns of another sheet, but how do i get it to paste the code all in 1 column of a new sheet?

    So for example, sheet 1 has column A B C D. I want to copy it to sheet 2 with the format of:
    A
    B
    C
    D

  4. Hello,
    I tried to use the code for my workbook, but I was not successful.
    I have 23 different sheets, each sheet have 355 columns. I would like to copy each similar column in all 23 sheets into a new one. For instance, I would like to copy all "B" columns in the 23 different sheets into a new sheet, in a way that the new sheet would contain 23 columns. At the end, I will have 355 sheets, and each sheet would contain 23 columns.
    It would be much appreciated if you can help me achieve this.
    Many Thanks.

  5. Hi,

    I have a workbook with multiple sheets . I have to create a new workbook with headers and copy multiple columns from different sheets and paste into the new workbook which has headers. please help.

  6. Hi,
    I have a sheet with data that needs to copied to a different sheet based on different conditions. There are two conditions possible and if first condition is met, I need 5 sets of 3 columns (So set 1 is columns D to F, set 2 is G to I, so on...) copied in different rows and if second condition is met, another 5 sets of 3 columns in different row. If any of those sets are empty, don't copy.
    Thanks for your help.

  7. i have a workbook containing 200+ sheets. I want to copy from each sheet from only two columns i.e. column A and column D in two columns in another sheet, lets say that MASTER. So each sheet data of column A should appear in column A of MASTER, similarly each sheet data in column D should appear in column B of MASTER.
    The headers in each sheet does not start from row #1 rather in row 14 and data starts from row 15. can you kindly help?

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