radneo
07-30-2014, 08:11 AM
Hello,
I have a workbook with several worksheets. Each worksheet has a column of data (of the same length). I need to copy all the columns from the various sheets into one sheet side-by-side (i.e., in a 2D array/matrix format). I got the following code from the forums (thanks to mdmackillop!) which takes 2 columns from 2 sheets and put them side-by-side. Can someone please help modify the code so it can copy the columns from all the sheets at once? The number of sheets in the book is known in advance. I have attached my EXCEL file for reference. Any help will be greatly appreciated! Thank you!!12042
Option Explicit
Sub JoinData()
Dim i As Long, Cols As Long
Application.ScreenUpdating = False
Sheets("DP_RP_IBDs_3").Activate
Cells.Copy
Sheets("new").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cols = Sheets("new").[IV1].End(xlToLeft).Column
For i = Cols To 2 Step -1
Cells(1, i).EntireColumn.Insert
Next
For i = 1 To Cols
Sheets("DP_RP_IBDs_4").Activate
Columns(i).Copy
Sheets("new").Activate
Cells(1, 2 * i).Select
ActiveSheet.Paste
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have a workbook with several worksheets. Each worksheet has a column of data (of the same length). I need to copy all the columns from the various sheets into one sheet side-by-side (i.e., in a 2D array/matrix format). I got the following code from the forums (thanks to mdmackillop!) which takes 2 columns from 2 sheets and put them side-by-side. Can someone please help modify the code so it can copy the columns from all the sheets at once? The number of sheets in the book is known in advance. I have attached my EXCEL file for reference. Any help will be greatly appreciated! Thank you!!12042
Option Explicit
Sub JoinData()
Dim i As Long, Cols As Long
Application.ScreenUpdating = False
Sheets("DP_RP_IBDs_3").Activate
Cells.Copy
Sheets("new").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cols = Sheets("new").[IV1].End(xlToLeft).Column
For i = Cols To 2 Step -1
Cells(1, i).EntireColumn.Insert
Next
For i = 1 To Cols
Sheets("DP_RP_IBDs_4").Activate
Columns(i).Copy
Sheets("new").Activate
Cells(1, 2 * i).Select
ActiveSheet.Paste
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub