siva.apps123
New member
- Joined
- Oct 23, 2017
- Messages
- 4
- Reaction score
- 0
- Points
- 0
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.
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.
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.
Code:
Sub Print_Preview()[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000]' Print Preview and automatic page numbers[/COLOR]
[COLOR=#000000] Dim xVPC, xHPC, xNumPage As Integer[/COLOR]
[COLOR=#000000] Dim xVPB As VPageBreak[/COLOR]
[COLOR=#000000] Dim xHPB As HPageBreak[/COLOR]
[COLOR=#000000] Dim xLastrow As Long[/COLOR]
[COLOR=#000000] Dim xWs As Worksheet[/COLOR]
[COLOR=#000000] Dim findRowNumber As Long[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000] Sheets(Array("TitlePage", "Preview")).Select[/COLOR]
[COLOR=#000000] Application.PrintCommunication = False[/COLOR]
[COLOR=#000000] With ActiveSheet.PageSetup[/COLOR]
[COLOR=#000000] .LeftMargin = Application.InchesToPoints(0.25)[/COLOR]
[COLOR=#000000] .RightMargin = Application.InchesToPoints(0.25)[/COLOR]
[COLOR=#000000] .TopMargin = Application.InchesToPoints(0.25)[/COLOR]
[COLOR=#000000] .BottomMargin = Application.InchesToPoints(0.25)[/COLOR]
[COLOR=#000000] .HeaderMargin = Application.InchesToPoints(0.3)[/COLOR]
[COLOR=#000000] .FooterMargin = Application.InchesToPoints(0.5)[/COLOR]
[COLOR=#000000] .PrintHeadings = False[/COLOR]
[COLOR=#000000] .PrintGridlines = False[/COLOR]
[COLOR=#000000] .PrintComments = xlPrintNoComments[/COLOR]
[COLOR=#000000] .PrintQuality = 600[/COLOR]
[COLOR=#000000] .CenterHorizontally = False[/COLOR]
[COLOR=#000000] .CenterVertically = False[/COLOR]
[COLOR=#000000] .Orientation = xlLandscape[/COLOR]
[COLOR=#000000] .Draft = False[/COLOR]
[COLOR=#000000] .PaperSize = xlPaperLetter[/COLOR]
[COLOR=#000000] .FirstPageNumber = xlAutomatic[/COLOR]
[COLOR=#000000] .Order = xlDownThenOver[/COLOR]
[COLOR=#000000] .BlackAndWhite = False[/COLOR]
[COLOR=#000000] .Zoom = True[/COLOR]
[COLOR=#000000] .FitToPagesWide = 1[/COLOR]
[COLOR=#000000] .FitToPagesTall = False[/COLOR]
[COLOR=#000000] .PrintErrors = xlPrintErrorsDisplayed[/COLOR]
[COLOR=#000000] .OddAndEvenPagesHeaderFooter = False[/COLOR]
[COLOR=#000000] .ScaleWithDocHeaderFooter = True[/COLOR]
[COLOR=#000000] .AlignMarginsHeaderFooter = True[/COLOR]
[COLOR=#000000] .CenterFooter = "&""Arial,Bold""&12Total Number of Repairs to this point= " & Chr(10) & " Page: &P of &N"[/COLOR]
[COLOR=#000000] .FirstPage.CenterFooter.Text = "&""Arial,Bold""&14Report Header- Page: &P"[/COLOR]
[COLOR=#000000] .DifferentFirstPageHeaderFooter = True[/COLOR]
[COLOR=#000000] End With[/COLOR]
[COLOR=#000000] Sheets("Preview").Select[/COLOR]
[COLOR=#000000] Range("o8:P8").Clear[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000]' Application.Goto Reference:="Print_Area" Used_Range[/COLOR]
[COLOR=#000000]' Selection.Rows.AutoFit[/COLOR]
[COLOR=#000000] With ActiveSheet.PageSetup[/COLOR]
[COLOR=#000000] .PrintTitleRows = "$1:$14"[/COLOR]
[COLOR=#000000] End With[/COLOR]
[COLOR=#000000] 'inserting page breaks at regular intervels[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000] Set xWs = Sheets("Preview")[/COLOR]
[COLOR=#000000] Set findRow = Sheets("Preview").Range("A:A").Find(What:="VIN", LookIn:=xlValues)[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000] findRowNumber = findRow.Row + 2[/COLOR]
[COLOR=#000000] xRow = 35[/COLOR]
[COLOR=#000000] xWs.ResetAllPageBreaks[/COLOR]
[COLOR=#000000] xLastrow = xWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row[/COLOR]
[COLOR=#000000] For i = xRow + findRowNumber To xLastrow Step xRow[/COLOR]
[COLOR=#000000] xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)[/COLOR]
[COLOR=#000000] Next[/COLOR]
[COLOR=#000000] Application.PrintCommunication = True[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000]End Sub[/COLOR]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.
Last edited by a moderator:
