Custom Page Break VBA Code

[Jack58]

I am using the code listed below to automatically set my print ranges on my reports. The problem I am having is that if the document has no page breaks, the code works great, however if the report is Sub-Totaled and placing a page break in the document the code below will only print the first page.

Any example, I have a document that have information for Buyers 1 through 10. I want to have a separate page for each buyer, so I Subtotal by Buyer and Check “Page Break Between Groups”, again when I do the code will only print the first page, I need it to print the entire range of the document.






[vba]Sub SetPrintRanges(StartCell As String)

Dim i As Integer
'Dim StartCell As String
Dim CheckColumn As Integer

'StartCell = "A12"

For i = 1 To Sheets.Count
CheckColumn = 0
Sheets(i).Select
Range(StartCell).Select
Do Until CheckColumn = 4
If ActiveCell.Value = vbNullString Then
CheckColumn = CheckColumn + 1
Else
CheckColumn = 0
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(0, -5).Select
Do Until ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Loop
Worksheets(i).PageSetup.PrintArea = StartCell & ":" & Chr(64 + ActiveCell.Column) _
& CStr(ActiveCell.Row - 1)
Range(StartCell).Select
Next i

[vba/]End Sub


Hope someone can help me out here.

Thanks

Jack

 

[SkipVought]

Hi,

You really don't need all that code. You already have you page breaks using the Subtotal Feature.

Worksheets(i).PageSetup.PrintArea = Range(StartCell).CurrentRegion

as long as the cells contiguous to StartCell define your print area.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244