Printing problem

[FiluDlidu]

Hi all,

Let's say I have long list of data in columns A, B and C, which in the end
makes it a long skinny table. I would like a print to "wrap" the table so
that it can go 3 or 4 times from the top to the bottom of the page (we'll
assume it's a landscape printing or an 11x17 sheet...), without losing the
continuity of the data and without having to physically move the existing
data residing in the A, B and C columns.

Any ideas???

Thanks,
Feelu.

 

[Gord Dibben]

There are a couple of ways to wrap the text.

Try this and see if it is what you need.

Snakes three columns to nine columns.

Public Sub Snake3to9()
Dim myRange As Range
Dim colsize As Long
Dim maxrow As Long
Const numgroup As Integer = 3
Const NUMCOLS As Integer = 9
On Error GoTo fileerror
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
((NUMCOLS - 1)) / NUMCOLS)) / numgroup
MsgBox "Number of Rows to Move is: " & colsize
Range("A1").Select
With ActiveCell.Parent.UsedRange
maxrow = .Cells(.Cells.Count).Row + 1
End With
ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _
.End(xlUp).Offset(1, 0).Select
Set myRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 1)).Address)
myRange.Cut Destination:=ActiveSheet.Range("A1").Offset(0, _
(NUMCOLS - numgroup))
Range("A1").Select
Cells.End(xlDown).Offset(1, 0).Select
Set NextRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 1)).Address)
NextRange.Cut Destination:=ActiveSheet.Range("A1").Offset(0, _
(NUMCOLS / numgroup))
Application.CutCopyMode = False
Range("A1").Select
fileerror:
End Sub


Gord Dibben MS Excel MVP

 

[FiluDlidu]

Gord,

Thanks a lot for your reply! Now... this is a little much for me... I have
a few questions:

Where in VB editor do I paste this code?

When and how does printing occur and how do I tell the printer that the code
needs to be read and apply to the print job?

Is there also a way to repeat the first two rows at the top of every column
on the print job?

Hope this is not too much asking...

Feelu

 

[Jack Sons]

Gord,

I tried to modify your code so it woud be applicable for the general case
that we have a range i columns wide wich we want to "snake" to j colums,
where j is a multiple (k*i) of i (without having blank columns). But I
failed.

Please be so kind to provide the code. Thanks in advance.

Jack Sons
The Netherlands

 

[Gord Dibben]

Alt + F11 to open the VBEditor. CTRL + r to open the Project Explorer.

Right-click on your workbook/project and Insert>Module.

Paste into that module.

The code only moves the 3 columns of data to 9 columns.

Printing and Print Setup is up to you.

Turn on the macro recorder when doing the print setup.


Gord

 

[Gord Dibben]

Jack

Suggest you visit David McRitchie's site for his SnakeCols routine.

http://www.mvps.org/dmcritchie/excel/code/snakecol.txt


Gord

 

[Jack Sons]

Enormous! Thanks Gord.

Jack.

Jack

Suggest you visit David McRitchie's site for his SnakeCols routine.

http://www.mvps.org/dmcritchie/excel/code/snakecol.txt


Gord