Can I print each row on a separate page?

[Stuck]

I have a 5,000 row spreadsheet and I need to print each row on it's own page.
Is there an automated way of doing this?

 

[Jim Thomlinson]

Here is some code that you can use. Place it in a standard code module (the
same place as recorded macro code ends up). You can assign it to a command
button or just run it directly. It is set to do a print preview instead of
print out. Once you are sure that it works ok then change the code as
described right in the code.

Public Sub PrintOneLine()
Dim rng As Range

For Each rng In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
With rng.Parent
.PageSetup.PrintArea = rng.EntireRow.Address
'.PrintOut 'remove the apostrophy
.PrintPreview 'Add an appostrophy
End With
Next rng
End Sub

 

[Stuck]

Jim;
Thank you so so much! I'm embarrassed to ask this, but where do I put this
code? I've never used a code module or command button? Thanks again.

 

[Gord Dibben]

That's a lot of trees!!

Not sure if I should support this operation<g>

In a helper column adjacent to your data enter the numbers 1 through 5000

Start this macro then go for a coffee.

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("A1") 'edit to helper column
For Each rng In Range("A1:A5000") 'edit to helper column
If rng.text <> OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub

Then print the sheet on your 5000 sheets of paper.


Gord Dibben MS Excel MVP

 

[Luke M]

Open the Visual Basic Editor by pressing Alt+F11 (or Tools - Macro - Visual
Basic Editor). Goto Insert - Module.
Paste Jim's code in. Close the editor.

Back in your workbook, you can either run the macro directly ( under
Tools-Macro-Macro, or Alt+F8) or you can assign the macro to a object. This
can be something drawn with the drawing toolbar (right click on shape, assign
macro) or a command button (goto View - Toolbars - forms, by default its the
4th button on the toolbar). You can give the command button a name and assign
a macro to it.

 

[Stuck]

I inserted the code on a much smaller (5 row) spreadsheet. The code does
highlight each row consecutively and stops at the last row. However, I don't
see any print previews, so I trried the code modification to see if would
print, but still no luck. I have Excel 2003. Any ideas on what I'm doing
wrong. Thanks again.

 

[Stuck]

It worked.!!! Yea!! I did a test on a 5 row spreadsheet. Is there any way to
supress the "helper column" from printing (I used column 1 for sequencing and
the data is in column 2)? Thanks very much.

 

[Gord Dibben]

Hide Column 1 if you don't want it printed.

Or delete it after you have save the workbook with your pagebreaks set.


Gord

 

[Ben Gillett]

Hi Jim,

Is it possible to do this with columns instead of rows? i.e print each column onto a separate page

Cheers

 

[Clif McIrvin]

Try changing these three lines in Jim's code as follows:

Public Sub PrintOneColumn()

For Each rng In Range(Range("A1"), Cells(1,
Columns.Count).End(xlToLeft))

..PageSetup.PrintArea = rng.EntireColumn.Address

Hi Jim,

Is it possible to do this with columns instead of rows? i.e print each
column onto a separate page

Cheers

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[ankit bansal]

ipls help me....!!!
i have an excel sheet of approx 100-500 rows.
i have to print data of each row to a different page in .prn file.
hw can i do this..???
pls rep soon...
thnx in advance...

1 more thing i would like to add that i have approx 30-40 columns, so, when i used ur above code, i got each row separately, but i m getting 1 row in 4 pages because of 30 columns...
what can i do...
pls help soon...