Copy single column from different sheets and paste it in master sheet side by side

prashantkochar263

New Member
Joined
Jul 19, 2018
Messages
15
I have a workbook that has 500+ sheets naming sheet1, sheet 2,sheet3......sheet500...each sheet has multiple columns. I need to copy 'M column' of each sheet and paste it in a master sheet.
like from sheet1.. M column in master sheet in A Column, from sheet2 ..M column in master sheet in B column, from sheet 3.. M column in master sheet C column ...... llike that....
I am new to VBA.. AND REQUIREMENT THIS URGENT....
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Is the Master sheet named Master
And sheet named Master is first sheet in workbook.

So copy column M form sheets 2 to last sheet
and Paste into column A of master sheet and then next time copy column M of sheet 3 into column B of master sheet. Is that what you want?

Do we need to copy formatting also or just the values from the columns
 
Upvote 0
YES master sheet name is Master and just values required. side by side.from sheet1 to last sheetxxx...(so total sheets are Master+sheetxxx)...Sheet name can be changed if required that's not an issue..
 
Upvote 0
Well we will be taking values from sheet2 to last sheet and entering them in sheet(1) which is named Master
The Master sheet must be in far left position on tab bar making it sheet(1)

Does this work for you.
If so use this script.

Not sure how long this may take 500 plus sheets is a lot.
Someone else here may have a faster way.
Please be sure and have a backup copy of this workbook before attempting this.

Code:
Sub Copy_Columns()
'Modified  7/19/2018  2:19:06 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim lastrow As Long
    For i = 2 To Sheets.Count
        lastrow = Sheets(i).Cells(Rows.Count, "M").End(xlUp).Row
        Sheets("Master").Cells(1, i - 1).Resize(lastrow).Value = Sheets(i).Cells(1, "M").Resize(lastrow).Value
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
THANKS a lot it works like charm..... but I think in one sheet only 256 columns can be possible so i need to run it twice each time in different sheet.. is there any way around to avoid this.??
 
Upvote 0
No. Excel 2013 has 16,384 columns
If we are only copying values from Column M and you have 500 sheets that means sheet master would only need 500 columns.
 
Last edited:
Upvote 0
Sub NEW_COLUMNS()
'Modified 7/19/2018 2:19:06 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim lastrow As Long
For i = 2 To Sheets.Count
lastrow = Sheets(i).Cells(Rows.Count, "O").End(xlUp).Row
Sheets("Master").Cells(1, i - 1).Resize(lastrow).Value = Sheets(i).Cells(1, "O").Resize(lastrow).Value
Next
Application.ScreenUpdating = True
End Sub


using the above script to get values of column O BUT After running getting only 256 columns and
error stating Application Defined or object defined error..
 
Upvote 0
If you're using Excel 2003 or earlier, a worksheet only contains 256 columns.

If you're using Excel 2007 or later, the workbook has to be saved as an xlsx or xlsb file. If you're in compatibility mode (look at the application caption), you need to save, close, and reopen the workbook.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top