Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Snaking columns in Excel (2000 SR-1)

    have a list with 9000 rows and 2 columns. Is there a way of printing so that columns repeat on each page.

    i.e. printed page will have 8 columns - each begining where the previous left off

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    Isn't that under File-Page setup-Sheet tab. Do Over then Down instead of Down then Over?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    That's the answer I posted and deleted. It doesn't do what he wants; I think the best answer is to dump the columns to Word and column format them.
    -John ... I float in liquid gardens
    UTC -7DS

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    John, you assumed correctly, the page setup options do not do what I want. I am really looking for a result like a newspaper article.

    I can do in either word or access, but just wanted to make sure that there was no excel solution

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    It is possible in XL but it is not pretty. You can copy A1:B60 say into A1:B60 of a new sheet, A61:B120 into C160 of the new sheet, A121:B180 into E1:F60 of the new sheet and A181:B240 into G1:H60 of the new sheet. Then repeat this, copying A241:B300 into A61:B120 of the new sheet ..... Then put page breaks every 60 lines of the new sheet.

    You can either use the camera to do the copying, or write a macro. I doubt if it is worth the effort unless you will be doing it often.

    I think this came up in the old lounge and someone posted a sheet called Snaking but I cannot find it in my files, perhaps someone else kept a copy.

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

    Re: Snaking columns in Excel (2000 SR-1)

    You need a macro for that. Here is one I got from Bill Manville some time ago:

    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

    Now run the sub called PrintOnMultiplePages.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    It's clearly labeled "Page Order" not column order in Page Setup, Sheet.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Snaking columns in Excel (2000 SR-1)

    The Solution I use is quite simple if you have Access, and IF, I say IF your spreadsheet doesn't have any blank rows within the 9000.

    Set up a report in Access with Snaking Columns using a Linked spreadsheet as the table instead of an Access table.

    This way here, I'm not copying and pasting anything.

    FWIW

Posting Permissions

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