none
How to insert formula in footer while printing for each page? RRS feed

  • Question

  • hi to all experts,

    I have a big sheet with some columns i want to insert sum of one column for each page while printing  and also wanna have total sum of each page with previous pages in footer.

    pls let me have your good idea in this respect. 


    Monday, September 23, 2013 5:25 AM

All replies

  • Hello,

    Try something like this (A is the column you need to sum, B is the column to put the Sum and the cumulative Sum)

    Sub Test()

    Dim PageNumberRow As Long
    Dim i As Long
    Dim SumColumnIs As Double
    Dim TotalSumColumn As Double
    Dim PageStartAtRow As Long

    Const SumColumn As String = "A"
    Const ColumnIs As String = "B"

    Application.ScreenUpdating = False
    ActiveWindow.View = xlPageBreakPreview

    PageStartAtRow = 1

    For i = 1 To ActiveSheet.HPageBreaks.Count + 1
        On Error Resume Next
        PageNumberRow = ActiveSheet.HPageBreaks(i).Location.Row - 1
        If Err.Number > 0 Then
            'Last page
            PageNumberRow = Cells(Rows.Count, SumColumn).End(xlUp).Row
        End If
       
        Err.Clear
        On Error GoTo 0
       
        SumColumnIs = Application.WorksheetFunction.Sum(Range("" & SumColumn & "" & PageStartAtRow & ":" & SumColumn & "" & PageNumberRow & ""))
        TotalSumColumn = TotalSumColumn + SumColumnIs
        Cells(PageNumberRow, ColumnIs) = "Sum= " & SumColumnIs & ", Total Sum = " & TotalSumColumn

        PageStartAtRow = PageNumberRow + 1
    Next i


    ActiveWindow.View = xlNormalView
    Application.ScreenUpdating = True


    End Sub


    Guy Zommer

    • Proposed as answer by Guy Zommer Tuesday, October 1, 2013 1:01 PM
    Monday, September 23, 2013 7:30 AM
  • dear Zommer,

    thanks for your above cod ,

    i want to have sum and total sum in footer of each page while printing or print preview

    pls comment.

     


    Monday, September 23, 2013 8:43 AM
  • dear Zommer,

    thanks for your above cod ,

    i want to have sum and total sum in footer of each page while printing or print preview

    pls comment.

     

    pls find a copy of my file  at 

    http://s1.picofile.com/file/7947102468/sample_sheet_MS.xlsm.html

    i assigned your cod to a c button also add sum for another column.

    i want to insert content of cell "F " in footer of each related page.

    thanks in advance.



    Monday, September 23, 2013 11:06 AM
  • Try this:

    Sub Test()

    Dim PageNumberRow As Long
    Dim i As Long
    Dim SumColumnIs As Double
    Dim TotalSumColumn As Double
    Dim PageStartAtRow As Long
    Dim TheRange As Range
    Dim FirstRow As Long

    Const SumColumn As String = "A"
    Const ColumnIs As String = "A"

    Application.ScreenUpdating = False
    ActiveWindow.View = xlPageBreakPreview

    PageStartAtRow = 1
    FirstRow = PageStartAtRow

    For i = 1 To ActiveSheet.HPageBreaks.Count + 1
        On Error Resume Next
        PageNumberRow = ActiveSheet.HPageBreaks(i).Location.Row - 1
        If Err.Number = 0 Then
            Rows(PageNumberRow + 1).Insert
        Else
            PageNumberRow = Cells(Rows.Count, SumColumn).End(xlUp).Row
        End If
       
        On Error GoTo 0
        Err.Clear
       
       
        On Error Resume Next
        Set ActiveSheet.HPageBreaks(i).Location = Cells(PageNumberRow + 2, SumColumn)
        If Err.Number > 0 Then 'for last page
            ActiveSheet.HPageBreaks.Add before:=Cells(PageNumberRow + 2, SumColumn)
            ActiveSheet.PageSetup.PrintArea = "$" & SumColumn & "$" & FirstRow & ":$" & SumColumn & "$" & PageNumberRow + 1 & ""
        End If
       
        On Error GoTo 0
        Err.Clear
       
        SumColumnIs = Application.WorksheetFunction.Sum(Range("" & SumColumn & "" & PageStartAtRow & ":" & SumColumn & "" & PageNumberRow & ""))
        TotalSumColumn = TotalSumColumn + SumColumnIs
       
        Set TheRange = Cells(PageNumberRow + 1, ColumnIs)
        With TheRange
            .Font.Bold = True
            .Value = "Sum= " & SumColumnIs & ", Total Sum = " & TotalSumColumn
        End With

        PageStartAtRow = PageNumberRow + 2
    Next i


    ActiveWindow.View = xlNormalView
    Application.ScreenUpdating = True


    End Sub


    Guy Zommer

    Tuesday, September 24, 2013 4:25 AM
  • Try this:

    Sub Test()

    Dim PageNumberRow As Long
    Dim i As Long
    Dim SumColumnIs As Double
    Dim TotalSumColumn As Double
    Dim PageStartAtRow As Long
    Dim TheRange As Range
    Dim FirstRow As Long

    Const SumColumn As String = "A"
    Const ColumnIs As String = "A"

    Application.ScreenUpdating = False
    ActiveWindow.View = xlPageBreakPreview

    PageStartAtRow = 1
    FirstRow = PageStartAtRow

    For i = 1 To ActiveSheet.HPageBreaks.Count + 1
        On Error Resume Next
        PageNumberRow = ActiveSheet.HPageBreaks(i).Location.Row - 1
        If Err.Number = 0 Then
            Rows(PageNumberRow + 1).Insert
        Else
            PageNumberRow = Cells(Rows.Count, SumColumn).End(xlUp).Row
        End If
       
        On Error GoTo 0
        Err.Clear
       
       
        On Error Resume Next
        Set ActiveSheet.HPageBreaks(i).Location = Cells(PageNumberRow + 2, SumColumn)
        If Err.Number > 0 Then 'for last page
            ActiveSheet.HPageBreaks.Add before:=Cells(PageNumberRow + 2, SumColumn)
            ActiveSheet.PageSetup.PrintArea = "$" & SumColumn & "$" & FirstRow & ":$" & SumColumn & "$" & PageNumberRow + 1 & ""
        End If
       
        On Error GoTo 0
        Err.Clear
       
        SumColumnIs = Application.WorksheetFunction.Sum(Range("" & SumColumn & "" & PageStartAtRow & ":" & SumColumn & "" & PageNumberRow & ""))
        TotalSumColumn = TotalSumColumn + SumColumnIs
       
        Set TheRange = Cells(PageNumberRow + 1, ColumnIs)
        With TheRange
            .Font.Bold = True
            .Value = "Sum= " & SumColumnIs & ", Total Sum = " & TotalSumColumn
        End With

        PageStartAtRow = PageNumberRow + 2
    Next i


    ActiveWindow.View = xlNormalView
    Application.ScreenUpdating = True


    End Sub


    Guy Zommer

    Dear Zommer,

    thank you for new cod but this is not proper to my request , as i mentioned i want to show sum and total sum of each page in Footer of that page.


    Tuesday, September 24, 2013 8:22 AM