different footer for every page.

[kzc275]

Hi,
I have a workbook with a macro which generates 147+ pages. i would like to have first page, last page and rest of the pages different. my footer should give the count of rows printed till the current page. I am also breaking pages such as each page has 35 rows in it. Below is the code i tried. issue is i am not getting different footer for different page. any other code is also welcome. Thank you.

Sheets("Preview").Select
    FirstDataRow = 15
    RowsPerPage = 35
    headrow = FirstDataRow - 1
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    PageCount = (FinalRow - headrow) / RowsPerPage
    PageCount = Application.WorksheetFunction.<wbr>RoundUp(PageCount, 0)
    Set xWs = Sheets("Preview")
    Set findRow = Sheets("Preview").Range("A:A")<wbr>.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)
          
        j = j + 1
        TotaltillthisPage = 7 * j
        
        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
        
        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

 

[Macropod]

Cross-posted at:
https://www.excelguru.ca/forums/showthread.php?8555-different-footer-for-every-page
http://www.vbaexpress.com/forum/showthread.php?61135-different-footer-for-every-page
https://www.ozgrid.com/forum/forum/...acros/1194421-different-footer-for-every-page
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Also, when posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.