Excel Spreadsheet

S

sissiechurch2

I am working on an excel spreadsheet which is 5 columns and approximately 180
rows. I would like to be able to make the data flow to the right side of the
paper before starting a new page to limit the number of pages used for the
report.
 
O

Otto Moehrbach

Please explain what you mean by: "make the data flow to the right side of
the paper before starting a new page"? Give an example of what you want to
happen when you enter data. HTH Otto
 
G

Gord Dibben

Are you willing and able to use a macro?

Do you want 10 columns and 90 rows to print
in a snaked fashion like 1-90 at left and 91-180 at right?

Public Sub Snake5_to_10()
Dim myRange As Range
Dim colsize As Long
Dim maxrow As Long
Const numgroup As Integer = 2
Const NUMCOLS As Integer = 5
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, (NUMCOLS - 1)).Address)
myRange.Cut Destination:=ActiveSheet.Range("F1")
Application.CutCopyMode = False
Range("A1").Select
fileerror:
End Sub

Or 10 columns with 1-50 at left and 51-100 at right?

Sub Five_to_Ten()
Dim iSource As Long
Dim iTarget As Long
iSource = 1
iTarget = 1
Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "F")
iSource = iSource + 100
iTarget = iTarget + 51
Loop Until IsEmpty(Cells(iSource, "A").Value)
End Sub


Gord Dibben MS Excel MVP
 
S

sissiechurch2

Thanks for the info, but I'm afraid the macros are a bit beyond my expertise!
 
D

Dave Peterson

Maybe you can copy|paste into MSWord and use it's Format|Columns to save paper.

If you're not doing many calculations, you may just want to keep that data in a
table in MSWord.
 
G

Gord Dibben

But you can learn to use them.

To use either of the two macros I posted...........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.



Gord Dibben MS Excel MVP

Thanks for the info, but I'm afraid the macros are a bit beyond my expertise!
Click to expand...

Gord Dibben MS Excel MVP
 
S

sissiechurch2

Thanks for the instructions. I've tried to use SnakeCols numerous times and
just can't get it to work. Don't know exactly what I'm doing wrong, but no
luck yet.
 
Top