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.
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
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.
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?
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.
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.
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.
Yes it can be done. Your query is unique, so I have created a special article just for your query. Here is the link:
https://www.exceltip.com/general-topics-in-vba/transpose-column-to-excel-sheets-using-vba.html
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
Let's say you are pasting in column F of sheet 1. Then you need to find the first blank row in column F and past the next column there. To find the last row you can use this article
https://www.exceltip.com/tips/how-to-find-the-last-row-in-microsoft-excel-2010.html
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.
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
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
Thanks a ton. you codes have helped me alot