I have workbook with many sheets. out of all i choose two sheets to print in a print preview format. for example sheet 1 and sheet 2 are the sheets i want to print preview . when i print my first page is sheet 1 with an footer (something like : page 1 header) and second sheet has data which is printed out as multiple pages. and footer from second page must be like :
Total Number of Repairs to this point = 7 (count of entries in the page. i have braked the pages so that only seven entries are in each sheet. )
Page: 2 of 146 ( since this is the second sheet and first page is header.).
Now i have 2 issues.
1) with the following code i am only able to print first page footer but not from the second sheet.
' Print Preview and automatic page numbers
Dim xVPC, xHPC, xNumPage As Integer
Dim xVPB As VPageBreak
Dim xHPB As HPageBreak
Dim xLastrow As Long
Dim xWs As Worksheet
Dim findRowNumber As Long
Application.PrintCommunication = False
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = True
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.CenterFooter = "&""Arial,Bold""&12Total Number of Repairs to this point= " & Chr(10) & " Page: &P of &N"
.FirstPage.CenterFooter.Text = "&""Arial,Bold""&14Report Header- Page: &P"
.DifferentFirstPageHeaderFooter = True
' Application.Goto Reference:="Print_Area" Used_Range
.PrintTitleRows = "$1:$14"
'inserting page breaks at regular intervels
Set xWs = Sheets("Preview")
Set findRow = Sheets("Preview").Range("A:A").Find(What:="VIN", LookIn:=xlValues)
findRowNumber = findRow.Row + 2
xRow = 35
xLastrow = xWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = xRow + findRowNumber To xLastrow Step xRow
xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)
Application.PrintCommunication = True
2) also i want my footer to be like
Total Number of Repairs to this point = 7 (7*1 .here 1 is page number -1 .)
Total Number of Repairs to this point = 14 (7*2 here 2 i page number -1)
i am new to vba and from my knowledge it is not possible to have formulas in footer. how can i do this.
thanks in advance.