Set Page Number to One After Page Break

[DavidW]

I have a spreadsheet with a list of rep #'s followed by data for each
rep. I've written a macro to create page breaks when the rep # changes.
Is there a way to have the footer for each rep set to page 1 and then
have each page incremented by 1 for that rep? For example, I might have
a list of three reps. The first rep has two pages, the second has one,
the third has five. Can I code the macro to insert footers of pages 1 &
2 for rep 1, page 1 for rep 2, and pages 1, 2, 3, 4, & 5 for rep3?

The macro below works for the page breaks.

Thanks.


Code:
--------------------
Dim NumRows As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

NumRows = Range("A65536").End(xlUp).Row 'get the row count

With ActiveSheet
FirstRow = 8
LastRow = NumRows
For iRow = FirstRow To LastRow
If Worksheets("Report").Cells(iRow, "C").Value _
<> Worksheets("Report").Cells(iRow + 1, "C").Value _
And Worksheets("Report").Cells(iRow + 1, "C").Value <> 0 Then
'insert page break when rep# changes and the cell is not blank
Worksheets("Report").HPageBreaks.Add Before:=Worksheets("Report").Cells(iRow + 1, "A")
Else
'do nothing
End If
Next iRow
End With

 

[DavidW]

Right now I am attempting to use a separate macro to reset the page
numbers. The code above inserts the page breaks. I would like to modify
the code below to set the variable p and then the page number to 1 for
manual page breaks. For automatic page breaks, p will be incremented by
1 and then the page number set to p. I don't know how to specify which
page on the worksheet should be set equal to p. Is there a way to do
that? I believe the line of code "ActiveSheet.PageSetup.RightFooter =
p" will have to be replaced or modified with a line specifying the page
of the worksheet. As it is now, each page will have the same page number
(not what I want).

Or is there a better way?

Thanks for any suggestions.


Code:
--------------------
Sub CheckForPageBreaks()
Dim i As Integer
Dim p As Integer
p = 0

For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(i).PageBreak = xlManual Then
'manual page break above row
'With ActiveSheet
p = 1
ActiveSheet.PageSetup.RightFooter = p
ElseIf Rows(i).PageBreak = xlAutomatic Then
'automatic page break above row
p = p + 1
ActiveSheet.PageSetup.RightFooter = p
End If
Next
End Sub