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.
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.
“CopyColumns” macro will insert a new sheet named as “Master” after the “Main” sheet. “Master” sheet will contain consolidated data from all the sheets.
Above code is used to insert new worksheets after the “Main” worksheet.
If Source.Name <> “Master” And Source.Name <> “Main” Then
Above code is used to restrict copy of data from the “Master” and “Main” sheet.
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”
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
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 firstname.lastname@example.org