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

[prashantkochar263]

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

 

[My Aswer Is This]

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

 

[prashantkochar263]

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

 

[My Aswer Is This]

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.

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

 

[prashantkochar263]

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

 

[My Aswer Is This]

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.

 

[prashantkochar263]

Thanks got all columns...

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks got all columns...

 

[prashantkochar263]

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

 

[shg]

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.