print spreadsheet columns as rows down the page in excel

[Guest]

I am using Excel 2003. How can I print a spreadsheet with each row being
printed on a separate page and have the columns of that row printed as
separate lines on the page? I would like to create a report that will print
as many pages as I have rows. Each page will contain selected columns from
the spreadsheet printed as one column per line. Being able to format the
page would be a plus.

 

[Gord Dibben]

95YJ

Are the number of columns per row consistent?

i.e do you want A1:M1 printed as A1:A13 or similar?

Gord Dibben Excel MVP

 

[Guest]

Yes, the number of columns per row are consistent. Basically I want to
repeat the heading of each column as column one one every page and the data
from the row as column two. All rows have the same fields. ie:

Page 1
a1 a2
b1 b2
c1 c2

Page 2
a1 a3
b1 b3
c1 c3

I would like to automatically be able to print the number of pages as there
are rows, excluding the first heading row. Another way to put it would be to
switch the columns with the rows and print one column per page repeating the
heading column on every page.

 

[Dave Peterson]

I'd create a new worksheet (I named mine Sheet2).

I'd set it up with the headers/margins--everything I needed to have set up for
printing.

Then I guessed that I could use Row 1 to find the range of cells to copy (each
column has a header?).

And column A to determine the rows to copy.

Option Explicit
Sub testme()

Dim DataWks As Worksheet
Dim PrintWks As Worksheet
Dim iRow As Long
Dim myHeaders As Range

Set DataWks = Worksheets("sheet1")
Set PrintWks = Worksheets("sheet2")

PrintWks.Cells.ClearContents

With DataWks
Set myHeaders = .Range("a1", .Cells(1, .Columns.Count).End(xlToLeft))

PrintWks.Range("A1").Resize(myHeaders.Columns.Count, 1).Value _
= Application.Transpose(myHeaders.Value)

For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
PrintWks.Range("b1").Resize(myHeaders.Columns.Count, 1).Value _
= Application.Transpose(.Cells(iRow, "A") _
.Resize(1, myHeaders.Columns.Count).Value)
With PrintWks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
End With
Next iRow
End With
End Sub