Sheets("Preview").Select

FirstDataRow = 15

RowsPerPage = 35

headrow = FirstDataRow - 1

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

'counting total number of pages

PageCount = (FinalRow - headrow) / RowsPerPage

PageCount = Application.WorksheetFunction.RoundUp(PageCount, 0)

'resetting page breaks like 35 lines per page

Set xWs = Sheets("Preview")

Set findRow = Sheets("Preview").Range("A:A").Find(What:="VIN", LookIn:=xlValues)

findRowNumber = findRow.Row + 2

xRow = 35

xWs.ResetAllPageBreaks

xLastrow = xWs.Range("A" & Rows.Count).End(xlUp).Row

For i = xRow + findRowNumber To xLastrow Step xRow

xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)

'different footer value calculation and different footer for each page.

j = j + 1

TotaltillthisPage = 7 * j

'different footer value calculation and different footer for last page.

If j = PageCount Then

Totallastpage = (FinalRow - FirstDataRow) / 5

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.CenterFooter = "&""Arial,Bold""&12Total Number of Repairs for Model Year 2017 = " & Format(Totallastpage, "#,##0") & vbNewLine & "Total Number of Repairs to this point= " & Format(TotaltillthisPage, "#,##0") & vbNewLine & " Page: &P of &N"

End With

Exit For

End If

'different footer value calculation and different footer for each page.

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.CenterFooter = "&""Arial,Bold""&12Total Number of Repairs to this point= " & Format(TotaltillthisPage, "#,##0") & vbNewLine & " Page: &P of &N"

End With

Next i