Posts by siva.apps123

    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

    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

    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.

    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.

    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.