Copying multiple columns into one column

[Canes70]

Hello,

I need to copy multiple columns (around 400) into one column. does anyone have a macro or know of a way to do this quickly
Column1 Column2 Column3 Column4
320000000016001 320001000016001 320002000016001 320004000016001
320000000016002 320001000016002 320002000016002 320004000016002
320000000016005 320001000016005 320002000016005 320004000016005
320000000016010 320001000016010 320002000016010 320004000016010
320000000016012 320001000016012 320002000016012 320004000016012
320000000016015 320001000016015 320002000016015 320004000016015
320000000016104 320001000016104 320002000016104 320004000016104


Column1

320000000016001
320000000016002
320000000016005
320000000016010
320000000016012
320000000016015
320000000016104
320001000016001
320001000016002
320001000016005
320001000016010
320001000016012
320001000016015
320001000016104
320002000016001
320002000016002
320002000016005
320002000016010
320002000016012
320002000016015
320002000016104
320004000016001
320004000016002
320004000016005
320004000016010
320004000016012
320004000016015
320004000016104

thanks

 

[circledchicken]

Hi,

Do the columns all have headers in row 1? Are they all the same number of rows? Do you want the new single column on a new sheet? Or on the same sheet with the old columns deleted?

 

[circledchicken]

As a simplified example, with your data like this:

Sheet1

	B	C
1	1	6
2	2	7
3	3	8
4	4	9
5	5	10

<thead>
</thead><tbody>
</tbody>

Excel 2010

Running this code:

Sub example()

Dim vIn     As Variant
Dim vOut    As Variant
Dim i       As Long
Dim j       As Long
Dim ub1     As Long
Dim ub2     As Long


vIn = Application.Transpose(Range("B1").CurrentRegion)
ub1 = UBound(vIn, 1)
ub2 = UBound(vIn, 2)
ReDim vOut(1 To ub1 * ub2, 1 To 1)
For i = 1 To ub1
    For j = 1 To ub2
        vOut(j + (ub2 * (i - 1)), 1) = vIn(i, j)
    Next j
Next i
Range("A1:A" & (ub1 * ub2)) = vOut


End Sub

Would give you this result:

Sheet1

	A	B	C
1	1	1	6
2	2	2	7
3	3	3	8
4	4	4	9
5	5	5	10
6	6
7	7
8	8
9	9
10	10

<thead>
</thead><tbody>
</tbody>

Excel 2010

Obviously you will need to tweak it for your particular situation but that is an example pattern you could try.

 

[Canes70]

Yes, they all have the same number of rows and yes they have a column header for each column. It doesn't matter if the new column is in a new sheet or the same sheet

 

[Tatts]

Sorry to hijack the thread, looks like OP's questions has been answered.
I made one a few weeks ago that did the opposite.
It took data from one long column and split it across columns A-I and i think 45 rows down to fill one page. It was very basic, i recorded the first copy/paste as a macro and then used that code and adjusted the references for all the other columns. It ended up as a really long repetitive code but it works. Anyone able to give an easier way to do it?
Small sample of the code

Sheets("Sheet 1").Select
    Range("A2:A47").Select
    Selection.Copy
    Sheets("Sheet 2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet 1").Select
    ActiveWindow.SmallScroll Down:=14
    Range("A48:A93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet 2").Select
    Range("B2").Select
    ActiveSheet.Paste

 

[circledchicken]

Yes, they all have the same number of rows and yes they have a column header for each column. It doesn't matter if the new column is in a new sheet or the same sheet

Ok, then perhaps try this - the result is output to column A on Sheet2:

Sub example()

Dim vIn     As Variant
Dim vOut    As Variant
Dim i       As Long
Dim j       As Long
Dim ub1     As Long
Dim ub2     As Long

With Sheets("Sheet1").Range("A1").CurrentRegion
    vIn = Application.Transpose(.Offset(1, 0).Resize(.Rows.Count - 1))
End With
ub1 = UBound(vIn, 1)
ub2 = UBound(vIn, 2)
ReDim vOut(1 To ub1 * ub2, 1 To 1)
For i = 1 To ub1
    For j = 1 To ub2
        vOut(j + (ub2 * (i - 1)), 1) = vIn(i, j)
    Next j
Next i
Sheets("Sheet2").Range("A1:A" & (ub1 * ub2)) = vOut

End Sub

Sample data before running the code on Sheet1:

Sheet1

	A	B	C
1	Col1	Col2	Col3
2	1	5	9
3	2	6	10
4	3	7	11
5	4	8	12

<tbody>
</tbody>

Excel 2010

Sample output on Sheet2 after running the code:

Sheet2

	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12

<tbody>
</tbody>

Excel 2010

 

[circledchicken]

Sorry to hijack the thread, looks like OP's questions has been answered.
I made one a few weeks ago that did the opposite.
It took data from one long column and split it across columns A-I and i think 45 rows down to fill one page. It was very basic, i recorded the first copy/paste as a macro and then used that code and adjusted the references for all the other columns. It ended up as a really long repetitive code but it works. Anyone able to give an easier way to do it?
Small sample of the code

Hi,

Perhaps try this reverse procedure:

Sub example()

Dim vIn     As Variant
Dim vOut()  As Variant
Dim i       As Long
Dim j       As Long
Dim ub1     As Long
Dim ub2     As Long

vIn = Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
ub1 = 9 ' max number of columns
ub2 = Application.RoundUp(UBound(vIn, 1) / ub1, 0)
ReDim vOut(1 To ub2, 1 To ub1)
For i = 1 To ub1
    For j = 1 To ub2
        On Error Resume Next
        vOut(j, i) = vIn(j + (ub2 * (i - 1)))
        On Error GoTo 0
    Next j
Next i
Sheets("Sheet2").Range("A1").Resize(ub2, ub1) = vOut

End Sub

This assumes:

• Your data starts in A1 of Sheet1 (with no header)
• The output is placed on Sheet2

 

[Canes70]

Thanks circledchicken. that wroked

 

[circledchicken]

Thanks circledchicken. that wroked

Your welcome.

 

[Tatts]

It works so long as i only have 450 lines in column A. If there are any more then the data will go onto the second page eg column A will have 1-50 then B will have 51-100.
After splitting it will be converted to pdf. I'm happy for it to go to a second page but want it to start on page 2 in column A50 (451) from the number after I50 (450).
I only know some very basic vba and i can't figure out how this works and not sure how to edit to make it do what i would like.
If you have time i'd be interested in a bit of an explanation on how your code works.