Prev: ASAP bout pivot table
Next: Seperate text and numbers in a column?
From: Concord on 10 Feb 2010 04:16
looking to convert each spreadsheet row to print as a column per page
--
Concord
From: JLatham on 10 Feb 2010 16:15
If you've got more than one or two rows to deal with, the optimum is probably
the macro below. You'll need to change the names of the two worksheets
involved, and the "EmptySheet" referred to may even have to be added to your
workbook if all the sheets in it are currently being used for something.

To put the code to work: Start by making a copy of your workbook to test
things with. Then open that copy and press [Alt]+[F11] and choose
Insert-->Module and then copy and paste the code below into the empty module,
edit to change the names of the two worksheets involved and close the VB
Editor.

Select the sheet with the rows to print as columns and then use Tools -->
Macro --> Macros and select the macro in the list and click the [Run] button.

Each row, one by one, will be printed as a single column on separate pages.

Sub SpecialPrint()
'need an empty sheet (named EmptySheet in this code)
'to receive the data to be printed
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim copyRange As Range
Dim lastRow As Long
Dim lastColumn As Long
Dim LC As Long ' loop counter

'change this to the name of the worksheet with the
'rows of data that need to be transposed and printed
'as a column
Set srcWS = ThisWorkbook.Worksheets("SourceRowsSheet")
'change name of sheet as required
Set destWS = ThisWorkbook.Worksheets("EmptySheet")
'pick a column that has entries in all cells
'or at least an entry in the last row that needs
'to be processed
lastRow = srcWS.Range("A" & Rows.Count).End(xlUp).Row
srcWS.Activate
Application.ScreenUpdating = False
For LC = 1 To lastRow
destWS.Cells.Clear ' clears contents and formats
lastColumn = srcWS.Cells(LC, Columns.Count).End(xlToLeft).Column
Set copyRange = srcWS.Range(Cells(LC, 1), Cells(LC, lastColumn))
copyRange.Copy
destWS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
'destWS.PrintOut copies:=1
Next ' end of LC loop
'housekeeping
Set copyRange = Nothing
Set srcWS = Nothing
Set destWS = Nothing
End Sub


"Concord" wrote:

> looking to convert each spreadsheet row to print as a column per page
> --
> Concord
 | 
Pages: 1
Prev: ASAP bout pivot table
Next: Seperate text and numbers in a column?