foce region to print on one page
 

I have a macro that generates a number of summary blocks and places them one
under the other.

The individual blocks are less than 10-rows high and are less than one page
wide.

Is there a way to make sure that no individual summary block is split
between two pages?

--
Richard

foce region to print on one page
 

Your post is a little vague to me, so I will have to make some assumptions.
You will need to set Horizontal Page Breaks every block to get each block on
its own page. I think you need to know how many blocks you have. I will
assum you have 15 and every block is exactly 10 rows long. You may have to
set your print area before you do this. Just Select all the 10 row "blocks"
and then Page Layout Set Print Area then run this code. Once your code has
ran you can choose which pages you want to print. Hope this helps.

Option Explicit

Sub PrintBlocks()

Dim i As Long

For i = 1 To 15
Sheets("Sheet1").HPageBreaks.Add Befo=Cells(i * 10 + 1, 1)
Next i

End Sub

Ryan


"Richard" wrote:

I have a macro that generates a number of summary blocks and places them one
under the other.

The individual blocks are less than 10-rows high and are less than one page
wide.

Is there a way to make sure that no individual summary block is split
between two pages?

--
Richard

foce region to print on one page
 

richard - i took a different approach. i'm working from the bottom
up, assuming that each block is more than one line wide, and that each
block is of varying widths........
=============================
Option Explicit

Sub print_sheets()

Dim myLastRow As Long
Dim myRange As Range
Dim myRange2 As Range
Dim i As Long

myLastRow = ActiveSheet.Cells(20000, 1).End(xlUp).Row

Do Until i = 1
Set myRange = ActiveSheet.Range("a" & myLastRow)
i = myRange.End(xlUp).Row
Set myRange2 = ActiveSheet.Range("a" & i & ":d" & myLastRow)
myRange.PrintOut
'MsgBox myRange2.Address

Set myRange = ActiveSheet.Range("a" & i)
myLastRow = myRange.End(xlUp).Row
Loop

End Sub
=========================
to test the macro it, i'd uncomment the MsgBox & comment out
the .PrintOut so you don't waste tons of paper. the messagebox will
give you the address of each block. my test block only went to column
D, so you'll need to change that........
hope it works for you!
:)
susan




On Jun 24, 3:22*pm, RyanH wrote:
Your post is a little vague to me, so I will have to make some assumptions. *
You will need to set Horizontal Page Breaks every block to get each block on
its own page. *I think you need to know how many blocks you have. *I will
assum you have 15 and every block is exactly 10 rows long. *You may have to
set your print area before you do this. *Just Select all the 10 row "blocks"
and then Page Layout Set Print Area then run this code. *Once your code has
ran you can choose which pages you want to print. *Hope this helps.

Option Explicit

Sub PrintBlocks()

Dim i As Long

* * For i = 1 To 15
* * * * Sheets("Sheet1").HPageBreaks.Add Befo=Cells(i * 10 + 1, 1)
* * Next i

End Sub

Ryan



"Richard" wrote:
I have a macro that generates a number of summary blocks and places them one
under the other.

The individual blocks are less than 10-rows high and are less than one page
wide.

Is there a way to make sure that no individual summary block is split
between two pages?

--
Richard- Hide quoted text -

- Show quoted text -