Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display Row of Data as Form/Page
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display Row of Data as Form/Page
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 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display Row of Data as Form/Page
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" wrote: 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 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display Row of Data as Form/Page
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" 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. ;-) "Jim Cone" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need 2 rows to display X axis data points for a line graph | Charts and Charting in Excel | |||
ranking query | Excel Discussion (Misc queries) | |||
Excel control cell display of data with passwords | Excel Worksheet Functions | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions |