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

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 19, 2018
Messages
15
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-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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
 

prashantkochar263

New Member
Joined
Jul 19, 2018
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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:

prashantkochar263

New Member
Joined
Jul 19, 2018
Messages
15
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

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
Top