Display Row of Data as Form/Page

[Guest]

If I have records in rows with various columns of data, is there any way to
get Excel to display the columns as rows, one record per page? In other words
to flip the columns and rows and print it out as if it were a data entry form?

For example, let's have a list of employees with their name address and
phone# as columns. Like this:
Name Address Phone#
Name 1
Name 2
Name 3

Can I get Excel to print out one page per employee like this?

Name: Name 1
Address:
Phone#:
----------
Name: Name 2
Address:
Phone#:
----------

I've looked at pivot tables and they don't actually "pivot" the rows and
columns, they just summarize data. I've seen the "data entry form" in Excel,
but its for entry not printing. I've tried to experiment with the "transpose"
function, but I can't get it to work either. It also doesn't look like it
would handle a variable number of records/rows very well either. I know I can
export or link to Access, but I really don't want to have to do that.

 

[Jim Cone]

What you haven't tried is VBA programming code.<g>
The following code goes in a standard module.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'The user must select the address list before running this code.
'Assumes list of addresses is always in 3 adjoining columns.
'Transposes data in each row onto a new temporary sheet and prints the sheet.
'Each address is placed starting at Cell B5 on the new sheet.
'One sheet is printed for each address.
'Jim Cone - San Francisco, USA - 04/03/2006
'-----------------------
Sub ShiftAndPrint()
On Error GoTo QuitPrinting
Dim rngAll As Excel.Range
Dim rngCell As Excel.Range
Dim objNewSheet As Excel.Worksheet

Application.ScreenUpdating = False
Set rngAll = Excel.Selection
Set objNewSheet = Worksheets.Add(Count:=1)

For Each rngCell In rngAll.Columns(1).Cells
rngCell.Resize(1, 3).Copy
objNewSheet.Range("B2").PasteSpecial Transpose:=True
objNewSheet.PrintOut
objNewSheet.Cells.Clear
Next
Application.DisplayAlerts = False
objNewSheet.Delete
QuitPrinting:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set rngCell = Nothing
Set rngAll = Nothing
Set objNewSheet = Nothing
End Sub
'-------------



"Tim Nicholson" <Tim (e-mail address removed)>
wrote in message...
If I have records in rows with various columns of data, is there any way to
get Excel to display the columns as rows, one record per page? In other words
to flip the columns and rows and print it out as if it were a data entry form?

For example, let's have a list of employees with their name address and
phone# as columns. Like this:
Name Address Phone#
Name 1
Name 2
Name 3
Can I get Excel to print out one page per employee like this?

Name: Name 1
Address:
Phone#:
----------
Name: Name 2
Address:
Phone#:
----------

I've looked at pivot tables and they don't actually "pivot" the rows and
columns, they just summarize data. I've seen the "data entry form" in Excel,
but its for entry not printing. I've tried to experiment with the "transpose"
function, but I can't get it to work either. It also doesn't look like it
would handle a variable number of records/rows very well either. I know I can
export or link to Access, but I really don't want to have to do that.

 

[Guest]

Thanks for the post! This code seems like it will do the trick. I had
*really* hoped there was some simpler way to have a second worksheet tab that
displayed the data in that format using links or whatever. I'm surprised that
this issue isn't addressed with a more standard user function. Of course,
that's what Access is for, I suppose. ;-)

 

[Jim Cone]

Tim,
Well then, give this a try. It may be almost as good as Access.<g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub ShiftAndPrint_R1()
On Error GoTo QuitPrinting
Dim rngAll As Excel.Range
Dim rngCell As Excel.Range
Dim objNewSheet As Excel.Worksheet
Dim lngN As Long

Application.ScreenUpdating = False
Set rngAll = Excel.Selection
Set objNewSheet = Worksheets.Add(Count:=1)

lngN = 2
For Each rngCell In rngAll.Columns(1).Cells
rngCell.Resize(1, 3).Copy
objNewSheet.Cells(lngN, 2).PasteSpecial Transpose:=True
lngN = lngN + 3
objNewSheet.Cells(lngN, 2).EntireRow.PageBreak = xlPageBreakManual
Next
Range("B2").Select
QuitPrinting:
Application.ScreenUpdating = True
Set rngCell = Nothing
Set rngAll = Nothing
Set objNewSheet = Nothing
End Sub
'-------------


"Tim Nicholson" <[email protected]>
wrote in message
Thanks for the post! This code seems like it will do the trick. I had
*really* hoped there was some simpler way to have a second worksheet tab that
displayed the data in that format using links or whatever. I'm surprised that
this issue isn't addressed with a more standard user function. Of course,
that's what Access is for, I suppose. ;-)