Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Split a list over 2 columns (XP>)

    Hi,
    I have a long list in Excel which spans 4 columns. In print preview, the list is over 4 A4 pages. Is it possible to have the list break at the bottom of the 1st page and create a new "column" of data, ie breaking A-D columns into F-I columns? Obviously at the bottom of page two, the remaining list items must also break into the F-I columns if the list extends past the page 2 pagebreak. And so forth. I want this to happen when I print the file, so as to save paper and make the list easier and shorter to view. When I close the file I will not save so the list will revert back to its A-D columns over 4 pages. I would like to retain the 4 column setup for sorting and grouping options. Only at printing must it break the list at page breaks over 2 4column groups.

    Tx
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split a list over 2 columns (XP>)

    Something like this:

    <pre>Sub PrintOnMultiplePages()
    Dim iRows As Integer, iCols As Integer
    iRows = Val(InputBox("How many rows per page?"))
    iCols = Val(InputBox("How many columns per page?"))
    If iRows <= 0 Or iCols <= 0 Then
    MsgBox "Invalid"
    Exit Sub
    End If
    Multi_ColumnPrint iRows, iCols
    End Sub

    Sub Multi_ColumnPrint(iRows As Integer, iCols As Integer)
    ' prints table starting at A1 in active sheet in multi-column format
    ' the first row of each page is titles
    ' there are iRows printed on each page including titles
    ' in iCols columns with a blank column between each set.
    Dim oActive As Object, oTemp As Object
    Dim iDestRow As Integer, iDestCol As Integer, i As Integer
    Set oActive = ActiveSheet
    ' create a temporary sheet to format the printout
    Set oTemp = Worksheets.Add
    oTemp.Name = "Temp"
    oActive.Activate
    ' copy the data into the desired numbers of columns
    ' assuming range to print is block starting A1
    ' and first row is headings

    With oActive.Range("A1").CurrentRegion
    ' set up headings
    .Rows(1).Copy
    For i = 1 To iCols
    With oTemp.Cells(1, (i - 1) * (.Columns.Count + 1) + 1)
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
    End With
    Next
    oTemp.PageSetup.PrintTitleRows = "$1:$1"
    iDestRow = 2
    iDestCol = 1
    For i = 2 To .Rows.Count Step iRows - 1 ' -1 because of heading row
    .Offset(i - 1).Resize(iRows - 1).Copy
    oTemp.Cells(iDestRow, iDestCol).PasteSpecial xlValues
    oTemp.Cells(iDestRow, iDestCol).PasteSpecial xlFormats
    If iDestCol = (iCols - 1) * (.Columns.Count + 1) + 1 Then
    ' have just done the last column of this page
    iDestCol = 1
    ' move down on destination sheet
    iDestRow = iDestRow + iRows - 1
    ' insert a page break
    oTemp.Cells(iDestRow, 1).PageBreak = xlManual
    Else
    iDestCol = iDestCol + .Columns.Count + 1 ' leave a spare column
    End If
    Next i
    End With
    ' print preview the temporary sheet
    oTemp.UsedRange.Columns.AutoFit
    oTemp.PrintPreview
    ' lose the temporary sheet
    Application.DisplayAlerts = False
    oTemp.Delete
    Application.DisplayAlerts = True
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split a list over 2 columns (XP>)

    WOW - thats quite a code essay you have written Jan Karel!

    My file is on my home PC. I will copy this code and paste it into a module in the workbook and test it out then. Tx for the code, I am sure it will work well! I will not be available for the next week, so I will let you know if the code worked on my file when I return.

    Cheers and tx again!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •