Format page to print columns on a page

[nysouth]

I have a small spreadsheet that needs to take the data in five columns, but when printing the five columns will arrange to be printed over two columns on the page.

Like this:

The data is stored in the workbook like this:

ColA ColB Col C Col D ColE
Name Address Phone Email Fax
Name Address Phone Email Fax
Name Address Phone Email Fax

But when it prints on the page I would like it to do this:

Left side of Page Right side of Page
Name Address Phone Email Fax Name Address Phone Email Fax
Name Address Phone Email Fax Name Address Phone Email Fax
Name Address Phone Email Fax Name Address Phone Email Fax
Name Address Phone Email Fax Name Address Phone Email Fax
Name Address Phone Email Fax Name Address Phone Email Fax
Name Address Phone Email Fax Name Address Phone Email Fax

The goal is to be able to add more records witout having to redo each page to keep in alphabetical order. The user really wants to keep the two column format to save paper.

Any help is greatly appreciated.

 

[fkegley]

I think you will need a macro to do this.
It should first sort the list into the desired order.
Once the sorting is finished, you will need to select the first row of data, copy it to a new worksheet (it does not have to be a new one). I would then have the macro build a range name that refers to the cell where I want the next row to be placed. I would then have macro return to original sheet, select next row, copy it to new sheet, etc., until all rows are copied.
Once all rows are copied, I would have macro switch to the list that is to be printed, select the range, and print. I would place a button on the original worksheet for the user to click. Everything else would be done by the macro.
If you need more than this, let me know.

Frank kegley
fkegley@hotmail.com

 

[DaleWatson123321]

Hi nysouth,

I've developed a model for you that involves routines for:

1) Setting up formulas on a separate "formula" sheet - that reference your original sheet.

2) Extracting the data from this separate sheet to a "report" sheet. This step is necessary because otherwise you'll be faced with having extra formulas on your formula sheet that will be referencing "blank cells" (that you don't want printed).

When the data is extracted, it's ONLY the data that's extracted and the data is "ready for printing".

Hope this helps.

It's probably best if you email me, and I'll send the file by return email.

As it's close to quitting time, I'll leave both my HOME and WORK addresses...

Regards, ...Dale Watson
HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[SkipVought]

Hi,

And here's a VBA solution...

Sub MakeColumns()
    Dim wsTemp As Worksheet, iCol  As Integer, lRow As Long, bNewCol As Boolean, r As Range, lRowCount As Long
    Application.ScreenUpdating = False
    Worksheets.Add
    Set wsTemp = ActiveSheet
    iCol = 1
    lRow = 1
    lRowCount = 0
    bNewCol = False
    With wsTemp
        For Each c In Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 1).End(xlDown))
            If lRowCount = 0 And Not bNewCol Then
                If c.EntireRow.PageBreak = xlPageBreakAutomatic Then GoSub NewCol
            Else
                If lRow = lRowCount Then GoSub NewCol
            End If
            For Each h In Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 1).End(xlToRight))
                .Cells(lRow, iCol + h.Column - 1).Value = c.Offset(0, h.Column - 1).Value
            Next
            lRow = lRow + 1
        Next
    End With
    Application.ScreenUpdating = True
    Exit Sub
NewCol:
    bNewCol = True
    lRowCount = lRow
    lRow = 1
    iCol = iCol + 2
    For Each h In Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 1).End(xlToRight))
        wsTemp.Cells(lRow, iCol + h.Column - 1).Value = Sheet1.Cells(1, h.Column).Value
    Next
    lRow = lRow + 1
    Return
End Sub

Just another option

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

sorry. us this code instead

Sub MakeColumns()
    Dim wsTemp As Worksheet, wsThis As Worksheet
    Dim iCol  As Integer, lRow As Long, bNewCol As Boolean, r As Range, lRowCount As Long
    Application.ScreenUpdating = False
    Set wsThis = ActiveSheet
    Worksheets.Add
    Set wsTemp = ActiveSheet
    iCol = 1
    lRow = 1
    lRowCount = 0
    bNewCol = False
    With wsTemp
        For Each c In Range(wsThis.Cells(1, 1), wsThis.Cells(1, 1).End(xlDown))
            If lRowCount = 0 And Not bNewCol Then
                If c.EntireRow.PageBreak = xlPageBreakAutomatic Then GoSub NewCol
            Else
                If lRow = lRowCount Then GoSub NewCol
            End If
            For Each h In Range(wsThis.Cells(1, 1), wsThis.Cells(1, 1).End(xlToRight))
                .Cells(lRow, iCol + h.Column - 1).Value = c.Offset(0, h.Column - 1).Value
            Next
            lRow = lRow + 1
        Next
    End With
    Application.ScreenUpdating = True
    Exit Sub
NewCol:
    bNewCol = True
    lRowCount = lRow
    lRow = 1
    iCol = iCol + 2
    For Each h In Range(wsThis.Cells(1, 1), wsThis.Cells(1, 1).End(xlToRight))
        wsTemp.Cells(lRow, iCol + h.Column - 1).Value = wsThis.Cells(1, h.Column).Value
    Next
    lRow = lRow + 1
    Return
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com