PDA

View Full Version : different footer for every page



siva.apps123
10-25-2017, 06:31 AM
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.RoundUp(PageCount, 0)
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)

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

siva.apps123
10-25-2017, 11:31 AM
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

macropod
10-26-2017, 01:20 AM
Cross-posted at:
https://www.excelguru.ca/forums/showthread.php?8555-different-footer-for-every-page
https://www.mrexcel.com/forum/excel-questions/1028588-different-footer-every-page.html
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/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.