printing in two column format like in word

[Adam Robinson]

Hi,

I have an excel spreadsheet with 4 columns. When I print it it takes 50
pages. There is enough room on the paper to accomodate a second set of
columns. In word there is the format, column, click on 2 option. Excel
doesn't seem to have that function. Do you know of anyway to do this in
excel?

i.e.
column 1 column 2
column 1 column 2 column 3 column 4 column 1 column 2 ....
Thanks

[Gord Dibben]

If a one-off job just select the bottom half of the 4 columns and cut/paste
to column E

You can also paste the 4 columns into a Word doc and use Word's column
formatting then back into Excel.

Or use a macro.

Public Sub Snake4to8()
Dim myRange As Range
Dim colsize As Long
Dim maxrow As Long
Const numgroup As Integer = 2
Const NumCols As Integer = 4
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("E1")
Application.CutCopyMode = False
Range("A1").Select
fileerror:
End Sub

Gord Dibben MS Excel MVP

On Thu, 23 Jul 2009 12:20:51 -0500, "Adam Robinson" <ada...@gmail.com>
wrote:

[Adam Robinson]

Thank you.  The script is not bad.  It would be better if the columns were from the same page.  Yeah it a bad description here is an example.  The source data is 200 rows of numbers from 1 to 800 ie.

1	2	3	4
5	6	7	8
9	10	11	12
13	14	15	16
17	18	19	20

rows cut

781	782	783	784
785	786	787	788
789	790	791	792
793	794	795	796
797	798	799	800

797 is row 200

What your script does:

1	2	3	4	401	402	403	404
5	6	7	8	405	406	407	408
9	10	11	12	409	410	411	412
13	14	15	16	413	414	415	416
17	18	19	20	417	418	419	420

page 2:

381	382	383	384	781	782	783	784
385	386	387	388	785	786	787	788
389	390	391	392	789	790	791	792
393	394	395	396	793	794	795	796
397	398	399	400	797	798	799	800

What it should do is:

1	2	3	4	209	210	211	212
5	6	7	8	213	214	215	216
9	10	11	12	217	218	219	220
13	14	15	16	221	222	223	224
17	18	19	20	225	226	227	228

end of page 1:

189	190	191	192	397	398	399	400
193	194	195	196	401	402	403	404
197	198	199	200	405	406	407	408
201	202	203	204	409	410	411	412
205	206	207	208	413	414	415	416

The 205 206 207 208 are the end of the first page.  Now see how 209 starts the second column.  The macro should take the second page and start the second column using that.
Does that make sense?

Thanks,


"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:9aoj6515aht79k1p2...@4ax.com...

[Gord Dibben]

Try this one based upon 800 rows.

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(52, 4).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 52, "A").Resize(52, 4).Cut _
Destination:=Cells(iTarget, "E")
iSource = iSource + 104
iTarget = iTarget + 53
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub

If not what you want, play with the resize(xx, 4)

Also make the appropriate changes to iSource + xx and iTarget + xx

Gord

On Fri, 24 Jul 2009 16:45:20 -0500, "Adam Robinson" <ada...@gmail.com>

[James White]

This works great. Thank you very much. You just saved me several hours
every month of manual cutting and pasting.

Thanks,

"Gord Dibben" <gorddibbATshawDOTca> wrote in message

news:sdfm651dr4k3u254p...@4ax.com...

[Adam Robinson]

Sorry it took so long to respond I was in training this week.

I tried the code and after tweaking the number of rows it is working
perfectly.

Thanks alot.
Adam

"Gord Dibben" <gorddibbATshawDOTca> wrote in message

news:sdfm651dr4k3u254p...@4ax.com...

[Gord Dibben]

Good to hear.

Thanks for the feedback.

Gord

On Fri, 31 Jul 2009 09:30:37 -0500, "Adam Robinson" <ada...@gmail.com>