Might be a long shot - Long Column to Page of Columns...

[jimmy2468]

This might be tough one, but I figured I'd post it... why not.

I have a long list of data (about 2000 cells - one column A1 - A2000). This data changes, and occasionally I have to print all the data on 8.5/11 paper.
Is there a quick way to format this long list such that the data arranges itself in subsequent columns until it is at the end of the printable area?

That is, if one page can fit A1 to A52 in the printable area,
and the printable area goes from columns A to I in width,
is there a quick way to organise A1 to A2000 so that A1 to A52 appears in A, A53 to A105 appears in column B and so on until it reaches column I, and then starts another print-area page?

Just checking... Thanks!

 

[pgc01]

Hi jimmy2468

An alternative to what you ask is to use an auxilliary worksheet where you arrange the data in a suitable way for printing.

In this example you have your data in Sheet1 in A1:A2000. You run the code and Sheet2 is cleared and receives the data arranged for printing.

Please try:

Sub PreparePrint()
Dim lrow As Long, lTargetRow As Long, lTargetCol As Integer
Const MaxCol As Integer = 9, MaxPageRow = 52

Worksheets("Sheet2").UsedRange.Clear
For lrow = 1 To 2000 Step MaxPageRow
    lTargetRow = 1 + MaxPageRow * (lrow \ (MaxPageRow * MaxCol))
    lTargetCol = 1 + (lrow Mod (MaxPageRow * MaxCol)) \ MaxPageRow
    Worksheets("Sheet2").Cells(lTargetRow, lTargetCol).Resize(MaxPageRow).Value = _
        Worksheets("Sheet1").Cells(lrow, 1).Resize(MaxPageRow).Value
Next
End Sub

Hope this helps
PGC

 

[pgc01]

Hi Again

From PM you'd like 2 other things:

- To be able to specify the Start and End rows to be printed.
- To copy just exactly until the End row

Please try:

Sub PreparePrint()
Dim lrow As Long, lTargetRow As Long, lTargetCol As Integer, iNRowsPrint As Integer
Const MaxCol As Integer = 9, MaxPageRow As Integer = 52
Const StartRow As Long = 270, EndRow As Long = 1206

Worksheets("Sheet2").UsedRange.Clear
For lrow = StartRow To EndRow Step MaxPageRow
    lTargetRow = 1 + MaxPageRow * ((lrow - StartRow) \ (MaxPageRow * MaxCol))
    lTargetCol = 1 + ((lrow - StartRow) Mod (MaxPageRow * MaxCol)) \ MaxPageRow
    iNRowsPrint = IIf(MaxPageRow <= EndRow - lrow, MaxPageRow, 1 + EndRow - lrow)
    Worksheets("Sheet2").Cells(lTargetRow, lTargetCol).Resize(iNRowsPrint).Value = _
        Worksheets("Sheet1").Cells(lrow, 1).Resize(iNRowsPrint).Value
Next
End Sub

This is of course just the core of the solution. You can add things like input boxes to ask the limit rows, to choose other column other than A, to choose other worksheets as source or target, etc.

Hope this is what you want
PGC

 

[jimmy2468]

Perfect! Thanks a lot.
J

 

[pgc01]

I'm glad it helped.

This is one of those I'll keep. You never know when you need it.

Cheers
PGC