Different first page footer and formulas in footer

vlsmithika

New Member
Joined
Oct 23, 2017
Messages
3
Hi,

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.

Sub Print_Preview()


' 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


Sheets(Array("TitlePage", "Preview")).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.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
End With

Sheets("Preview").Select

Range("o8:P8").Clear




' Application.Goto Reference:="Print_Area" Used_Range
' Selection.Rows.AutoFit

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$14"
End With

'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
xWs.ResetAllPageBreaks
xLastrow = xWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = xRow + findRowNumber To xLastrow Step xRow
xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)

Next

Application.PrintCommunication = True


End Sub

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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top