PDA

View Full Version : Combine Columns from different worksheets into one



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

p45cal
07-30-2014, 09:59 AM
try:
Option Explicit

Sub JoinData()
Dim DestnColm As Long, xxx As Worksheet, sht As Worksheet
With ActiveWorkbook
DestnColm = 1
Set xxx = .Sheets.Add(after:=.Sheets(.Sheets.Count))
For Each sht In .Worksheets
If Left(sht.Name, 3) = "DP_" Then
sht.UsedRange.Copy xxx.Cells(1, DestnColm)
DestnColm = xxx.UsedRange.Columns.Count + 1
End If
Next sht
End With
End Sub

radneo
07-31-2014, 10:14 AM
Awesome, thank you so much! This is perfect!!