i have a worksheet with very long rows (a to cf). For printing i would like
to wrap the rows to fit on a single page, and then seperate each set with a
space. I would also like to wrap the headers too, of course. is this
possible?

Say you're creating 10 columns per new row.

So it kind of looks like this:

$A$1 $B$1 $C$1 $D$1 $E$1 $F$1 $G$1 $H$1 $I$1 $J$1
$K$1 $L$1 $M$1 $N$1 $O$1 $P$1 $Q$1 $R$1 $S$1 $T$1
$U$1 $V$1 $W$1 $X$1 $Y$1 $Z$1 $AA$1 $AB$1 $AC$1 $AD$1
$AE$1 $AF$1 $AG$1 $AH$1 $AI$1 $AJ$1 $AK$1 $AL$1 $AM$1 $AN$1
$AO$1 $AP$1 $AQ$1 $AR$1 $AS$1 $AT$1 $AU$1 $AV$1 $AW$1 $AX$1
$AY$1 $AZ$1 $BA$1 $BB$1 $BC$1 $BD$1 $BE$1 $BF$1 $BG$1 $BH$1
$BI$1 $BJ$1 $BK$1 $BL$1 $BM$1 $BN$1 $BO$1 $BP$1 $BQ$1 $BR$1
$BS$1 $BT$1 $BU$1 $BV$1 $BW$1 $BX$1 $BY$1 $BZ$1 $CA$1 $CB$1
$CC$1 $CD$1 $CE$1 $CF$1

So column K is stacked under column A (and above all those others).

If one of those columns has a very wide value in it, then it could take a little
experimentation to see what looks the best. (Maybe stacking them 5 columns wide
would look better???)

You can try this and see if it works for you:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long

Dim iCol As Long
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim MaxCols As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

MaxCols = 10 '10 columns per row on the new worksheet

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 1
LastCol = .Range("Cf1").Column
End With

oRow = -1
With newWks
.Cells.NumberFormat = "@" 'text
For iRow = FirstRow To LastRow
oRow = oRow + 2
oCol = 0
For iCol = FirstCol To LastCol
If oCol = MaxCols Then
oCol = 1
oRow = oRow + 1
Else
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = curWks.Cells(iRow, iCol).Text
Next iCol
Next iRow
.UsedRange.Columns.AutoFit
End With
End Sub




Dave wrote:

i have a worksheet with very long rows (a to cf). For printing i would like
to wrap the rows to fit on a single page, and then seperate each set with a
space. I would also like to wrap the headers too, of course. is this
possible?

--

Dave Peterson

This was an excellent solution for me thanks Dave P.
RPP