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?

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
--
HTH...

Jim Thomlinson


"Stuck" wrote:

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;
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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Stuck" wrote:

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?

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

On Tue, 22 Sep 2009 11:48:02 -0700, Stuck
wrote:

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?

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Stuck" wrote:

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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Stuck" wrote:

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?

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.

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Stuck" wrote:

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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Stuck" wrote:

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?

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" wrote:

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

On Tue, 22 Sep 2009 11:48:02 -0700, Stuck
wrote:

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?

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

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


Gord

On Tue, 22 Sep 2009 14:05:01 -0700, Stuck
wrote:

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" wrote:

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

On Tue, 22 Sep 2009 11:48:02 -0700, Stuck
wrote:

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?