Printing a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list in alphabetical order of about 1,000 items which will continue
to update and grow.

I want to print them in columns similiar to newspaper style in MS Word
without cutting and pasting.

Can this be accomplished in Excel?
 
Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub

With 1000 items, enter 10 if you want 10 columns of 100 cells snaked.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

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 or edit 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
 
Select and Copy the list, Open Word, Set the number of columns, and
Paste your list in the upper left corner, Print.
 
Not sure what that means.

Maybe 10 columns with 50 rows each twice?

Can you provide more details please?


Gord
 
I can get it to do this, however, I am going to be updating this list on a
regular basis in excel and do not want to have to do that each time.
Thanks.
 
I would want the thousand items broken into 3 columns say 60 rows, or what
ever fits on the excel page so that it would truly stay and alphabetical list.

Hope this makes sense.
 
Just in case...
There is the commercial Excel add-in "Side by Side" from yours truly.
You can try it out just by asking for it ...
http://www.realezsites.com/bus/primitivesoftware
2, 3 or 4 up with your choice of rows per page.
--
Jim Cone
San Francisco, USA


"Nite Owl Sharon"
<[email protected]>
wrote in message
I have a list in alphabetical order of about 1,000 items which will continue
to update and grow.

I want to print them in columns similiar to newspaper style in MS Word
without cutting and pasting.
Can this be accomplished in Excel?
 
Thank you.
--
Nite Owl Sharon


Gord Dibben said:
How about 5 columns of 50 rows?

Since you will be adding to the master list we should use another sheet for
printing the 5-column list.

Add to then sort your master list first then run this macro.

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long
Dim wks As Worksheet
Set wks = ActiveSheet
If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Set copytosheet = Worksheets.Add
copytosheet.Name = "Copyto"
wks.Activate
Range("A1").Select
iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "B")
Cells(iSource + 100, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "C")
Cells(iSource + 150, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "D")
Cells(iSource + 200, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "E")
iSource = iSource + 250
iTarget = iTarget + 50
PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)
End If
End Sub

Gord
Click to expand...
 
How about 5 columns of 50 rows?

Since you will be adding to the master list we should use another sheet for
printing the 5-column list.

Add to then sort your master list first then run this macro.

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long
Dim wks As Worksheet
Set wks = ActiveSheet
If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Set copytosheet = Worksheets.Add
copytosheet.Name = "Copyto"
wks.Activate
Range("A1").Select
iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "B")
Cells(iSource + 100, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "C")
Cells(iSource + 150, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "D")
Cells(iSource + 200, "A").Resize(50, 1).Copy _
Destination:=Sheets("Copyto").Cells(iTarget, "E")
iSource = iSource + 250
iTarget = iTarget + 50
PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)
End If
End Sub

Gord
 
Hi.

Procedure 1 : Microsoft Word

Do the following:

1. Open a New Microsoft Document.
2. On the 'View" menu, point to "Toolbars" and then click Drawing. The
Drawing Toolbar displays on the Document interface.
3. Click the "Text Box" icon on the Drawing Toolbar and then draw as many
columns in the page.

Important: Insert page breaks for more pages. It will be difficult to add
pages later after you create cloumns on one page. You must press Enter for
the whole page to insert an additional page.

4. After drawing the textboxes on the page, click the first text box to
select. Right-click and select "Create Textbox link". The mouse pointer
changes its appearance and then click the other text box. This way you make a
link to the whole text boxes.

Procedure 2: Microsoft Excel

1.. Now open you Microsoft Excel Workbook and then select the Worksheet when
you want the data to be copied to Microsoft Word.

2.. Select the list and then copy it to the clipboard - On the Edit menu,
click Copy.
3.. Press Alt+TAB to select your Microsoft Word document.
4. Click inside the first Textbox to position your selection.
5. Click Paste. The list you copied from Microsoft Excel will automatically
flow across all the textboxes.

Note: Your selection of Textboxes could be in any order. The list will flow
based on criteria of your Textbox selection.

Important: For easy moving of data across these application, it is
recommended that Microsoft Word document is kept opened.


Challa Prabhu
 
Forgot to add this procdure for Printing:

Procedure 3: Microsoft Word.

Assuming that the active Word document is still open.

Do the folowing:

1. On the "File" menu, click "Print" to print the document with the list of
data in columns.

Challa Prabhu
 
Back
Top