Excel VBA Macro - Setting Page breaks for variable amounts of data

[commodityfever]

Hi all,


I trying to develop a macro to print my worksheets. Here's the problem, I never know how much data my end-users will incorporate into any one worksheet. In some instances, one worksheet can result in five pages or more of printout. How do I achieve the following:


1.) set a page break so that if, for example, I have 400 rows or 4000 rows of data that each page print 40 rows data.


2.) Print multiple row column headings on each page.


I would appreciate any help on the subject.


Hans Pottel provided a VBA solution


Sub PrintAreaWithpageBreaks()

Dim pages As Integer

Dim pageBegin As String

Dim PrArea As String

Dim i As Integer

Dim q As Integer

Dim nRows As Integer, nPagebreaks As Integer

Dim R As Range

Set R = ActiveSheet.UsedRange

'add pagebreak every 40 rows

nRows = R.Rows.Count

If nRows > 40 Then

nPagebreaks = Int(nRows / 40)

For i = 1 To nPagebreaks

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(40 * i + 1, 1)

Next i

End If

'can be used in a separate macro, as I Start counting the number of pagebreaks

pages = ActiveSheet.HPageBreaks.Count

pageBegin = "$A$1"

For i = 1 To pages

If i > 1 Then pageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address

q = ActiveSheet.HPageBreaks(i).Location.Row - 1

PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))

ActiveSheet.PageSetup.PrintArea = PrArea

' the cell in column 1 and in the row immediately below the pagebreak

' contains text for the footer

ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)

' ActiveSheet.PrintOut copies:=1

Next i

End Sub


Thanks & Regards

 

[Luke M]

Looks like your macro has #1 covered...was it not working?


For #2, you can go to Page Setup - Sheet, and select which rows you want to repeat on each sheet. You can either do this before hand, or record a short macro of you setting it up. Note that since this accesses the page setup, this will be a "slower" VB process. (For some reason, the page setup is connected to the printer driver, which takes awhile for VB to access)