Almost there

[Robert Gillard]

I posted the following question earlier in the week, to which I got the
reply underneath. This is really good except for one problem. I wanted a
pagebreak under every 7th "Total" found in column C.. this solution results
in the first pagebreak being under the 6th "total" but all of the rest are
correctly located under the 7th total from there on down., but because the
first break is out of position it puts all of the others one out.

I am not a programmer and do not really understand what everyline of code is
trying to do and whilst I have had a "fiddle" with some of the numbers in
the hope of hitting the right solution, I have not been lucky. Could anybody
see what the problem seems to be please.

Bob

I receive a spreadsheet that has to have 14 page breaks (across the page
only) inserted. The data varies in length each month ( by about 10 - 20
rows) and the page breaks need to be between "sections"

The good news is that in column C (along with other text) there is the word
"Total" in a cell on its own and this relates to each section. So ideally I
want a macro that will count down 7 "Totals" and put a page break underneath
this row, then count down another 7 "Totals" all down the spreadsheet which
will result in 14 page breaks in all.

Is this do-able and if so am I asking the right news group or should I be
in Programming or macros.

Bob

Sub InsertPageBreaks7()
Dim Counter As Long
Dim TotalCounter As Variant
Dim PageBreakscounter As Long
Dim LastRow As Variant
Dim i As Integer
0: Range("C1").Select
On Error Resume Next
For i = 1 To ActiveWindow.SelectedSheets.HPageBreaks.Count
ActiveWindow.SelectedSheets.HPageBreaks(i).Delete
Next i
LastRow = Range("C35200").End(xlUp).Row
If Cells(LastRow, 3) = "StopHere" Then
GoTo 10
Else
Cells(LastRow, 3).Offset(1, 0) = "StopHere"
End If

10: Do Until ActiveCell = "StopHere"
If Selection = "Total" Then
TotalCounter = TotalCounter + 1
If TotalCounter = 7 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add
Before:=ActiveCell.Offset(1, 0)
PageBreakscounter = 0
PageBreakscounter = PageBreakscounter + 1
If PageBreakscounter >= 14 Then Exit Sub
TotalCounter = 0
End If
End If
Selection.Offset(1, 0).Select ' move down
Loop

End Sub