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]

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.

I'm not really sure what you mean from your explanation. Do you mean 450 is the limit for each page (where a page is defined as 50 rows by 9 columns)? Something like this:

Sub example()

Dim vIn     As Variant
Dim vOut()  As Variant
Dim i       As Long
Dim j       As Long
Dim k       As Long

Const pgrows = 50
Const pgcols = 9
Const pgsize = pgrows * pgcols

With Application
    vIn = .Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    For k = 1 To .RoundUp(UBound(vIn) / pgsize, 0)
        ReDim vOut(1 To pgrows, 1 To pgcols)
        For i = 1 To pgcols
            For j = 1 To pgrows
                On Error Resume Next
                vOut(j, i) = vIn(((k - 1) * pgsize) + j + (pgrows * (i - 1)))
                On Error GoTo 0
            Next j
        Next i
        Sheets("Sheet2").Range("A" & (1 + ((k - 1) * pgrows))).Resize(pgrows, pgcols) = vOut
    Next k
End With

End Sub

 

[Tatts]

Sorry for the slow reply, i've been on a course and away from the PC for the last 2 weeks. This works really well, thanks heaps for taking the time to write it out.
I tried to modify it to allow for a blank row at the top of each page. I've managed this on the first page but not the subsequent pages. It also misses the numbers 442-450.

Sub example()
Dim vIn     As Variant
Dim vOut()  As Variant
Dim i       As Long
Dim j       As Long
Dim k       As Long
Const pgrows = [COLOR=#ff0000][B]49
[/B][/COLOR]Const pgcols = 9
Const pgsize = [COLOR=#ff0000][B](pgrows + 1)[/B][/COLOR] * pgcols
With Application
    vIn = .Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    For k = 1 To .RoundUp(UBound(vIn) / pgsize, 0)
        ReDim vOut(1 To pgrows, 1 To pgcols)
        For i = 1 To pgcols
            For j = 1 To pgrows
                On Error Resume Next
                vOut(j, i) = vIn(((k - 1) * pgsize) + j + (pgrows * (i - 1)))
                On Error GoTo 0
            Next j
        Next i
        Sheets("Sheet2").Range("A" & ([B][COLOR=#ff0000]2[/COLOR][/B] + ((k - 1) * pgrows))).Resize(pgrows, pgcols) = vOut
    Next k
End With
End Sub

I can put it in the code for the first page but i'm not sure how many pages will be required. Something very simple like:

Range("A1") = "Heading 1"
Range("A51") = "Heading 2"

Is it possible to put a heading at the top of each page that has numbers?

 

[Canes70]

Thanks, I give it a try

 

[circledchicken]

Sorry for the slow reply, i've been on a course and away from the PC for the last 2 weeks. This works really well, thanks heaps for taking the time to write it out.
I tried to modify it to allow for a blank row at the top of each page. I've managed this on the first page but not the subsequent pages. It also misses the numbers 442-450.

I can put it in the code for the first page but i'm not sure how many pages will be required. Something very simple like:
Is it possible to put a heading at the top of each page that has numbers?

Perhaps like this (the changes are highlighted in blue):

Sub example()

Dim vIn     As Variant
Dim vOut()  As Variant
Dim i       As Long
Dim j       As Long
Dim k       As Long


Const pgrows = 50
Const pgcols = 9
Const pgsize = pgrows * pgcols


With Application
    vIn = .Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    For k = 1 To .RoundUp(UBound(vIn) / pgsize, 0)
        ReDim vOut(1 To pgrows, 1 To pgcols)
        For i = 1 To pgcols
            For j = 1 To pgrows
                On Error Resume Next
                vOut(j, i) = vIn(((k - 1) * pgsize) + j + (pgrows * (i - 1)))
                On Error GoTo 0
            Next j
        Next i
        [COLOR=#000080][B]With [/B][/COLOR]Sheets("Sheet2").Range("A" & (1 + ((k - 1) * pgrows)))
[B][COLOR=#000080]            .Offset(k - 1, 0) = "Heading " & k[/COLOR][/B]
            .Resize(pgrows, pgcols)[B][COLOR=#000080].Offset(k, 0)[/COLOR][/B] = vOut
[COLOR=#000080][B]        End With[/B][/COLOR]
    Next k
End With


End Sub

 

[Tatts]

Brilliant. Thank-you very much. I changed the pgrows to 49 to stop the 50th row from being at the top of the 2nd page.

Waht is the best way to work out what each part of your code does, should i just use the help function to get a description for each command?

 

[circledchicken]

Brilliant. Thank-you very much. I changed the pgrows to 49 to stop the 50th row from being at the top of the 2nd page.

Ok, although I'm not too sure what you mean by that. Assuming your data starts in row 1 on Sheet1, the code I posted should do something like:

...Heading 1...
...50 rows of data...
...Heading 2...
...50 rows of data...
etc.

An easy way of testing the pattern might be just put the numbers 1 to 1000 in column A of Sheet1 and run the code to see the output in Sheet2.

Waht is the best way to work out what each part of your code does, should i just use the help function to get a description for each command?

Here is the code with comments that may help:

Sub example()

Dim vIn     As Variant
Dim vOut()  As Variant
Dim i       As Long
Dim j       As Long
Dim k       As Long


' define constants for max number of rows and columns and cells per page
Const pgrows = 50
Const pgcols = 9
Const pgsize = pgrows * pgcols


With Application
    ' read the Sheet1 data into the array vIn
    ' (note be careful when using the CurrentRegion property - http://msdn.microsoft.com/en-us/library/office/aa141370(v=office.10).aspx)
    vIn = .Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    
    ' figure out the number of pages required and loop through them
    For k = 1 To .RoundUp(UBound(vIn) / pgsize, 0)
        ' create a blank array vOut with the appropriate dimensions
        ReDim vOut(1 To pgrows, 1 To pgcols)
        ' add the data from vIn to the relevant parts of the vOut array
        For i = 1 To pgcols
            For j = 1 To pgrows
                ' On Error Resume Next is used as there may be less than pgsize cells required on the last page
                On Error Resume Next
                vOut(j, i) = vIn(((k - 1) * pgsize) + j + (pgrows * (i - 1)))
                On Error GoTo 0
            Next j
        Next i
        ' write the data and headings for the page to Sheet2
        With Sheets("Sheet2").Range("A" & (1 + ((k - 1) * pgrows)))
            .Offset(k - 1, 0) = "Heading " & k
            .Resize(pgrows, pgcols).Offset(k, 0) = vOut
        End With
    Next k
End With


End Sub

You can use the help function and also Google, there are lots of resources available to help with areas that might be unclear. Also this page from Chip Pearson's website provides a number of useful ways to step through and understand code in general:
Debugging VBA

 

[Tatts]

Ok, although I'm not too sure what you mean by that. Assuming your data starts in row 1 on Sheet1, the code I posted should do something like:

...Heading 1...
...50 rows of data...
...Heading 2...
...50 rows of data...
etc.

Thanks for the links, I'll put aside some time during work today to have a read through it

Possibly a better explanation that earlier (but i fixed it by changing 'Const pgrows = 49' instead of 50) - There are only 50 rows per page so by adding in the headings, the 50th row of data now appears on the first row of page two. 'Heading 2' is in A52 and is the second row of page 2. 'Heading 3' is in A103 and is the third row of page 3 etc.

Thanks again for all your help.

 

[circledchicken]

Thanks for the links, I'll put aside some time during work today to have a read through it

Possibly a better explanation that earlier (but i fixed it by changing 'Const pgrows = 49' instead of 50) - There are only 50 rows per page so by adding in the headings, the 50th row of data now appears on the first row of page two. 'Heading 2' is in A52 and is the second row of page 2. 'Heading 3' is in A103 and is the third row of page 3 etc.

Thanks again for all your help.

Ah ok, I see. Your welcome and thanks for the feedback.

 

[deakondiaz]

Your welcome.

*you're