Date and reference number being inserted into footer after the first page

[dpaton05]

How do I put things into the footer on all pages after the first using vba?

 

[Michael M]

RAnge("H" & lr+2).formula="=Sum("H1:H" & lr) / 2"

 

[dpaton05]

Thanks for all the help Michael, I have got it to work with this code, just needed a few ideas of the syntax to use

    Dim lr As Long
    lr = Cells(Rows.Count, "H").End(xlUp).Row
Dim cell As Range, s As Long, c As Long, curr As Currency

s = 1
c = 0
For Each cell In Range("H1:H" & lr)
    If cell.Offset(0, -2).Value = "Total incl. GST" Then
        'With r
         c = cell.Value + c
            '.Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
       ' End With
    End If
Next cell
curr = c
With ActiveSheet.PageSetup
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.CenterFooter.Text = ""
    'This inserts the date in the left footer
    .LeftFooter = Format(Now(), "mmmm d, yyyy")
    'This inserts the reference number and the total price in the footer at the centre, one of top of the other.
    .CenterFooter = ref3 & " " & Chr(10) & curr

End With

The number that is put in the footer is a straight number. How do I give it the currency format?

 

[Michael M]

Change this

curr = c

To

curr = Format(c, "Currency")

 

[dpaton05]

Is there a way to have the $ in front and comas after each three numbers?

 

[dpaton05]

I have also just noticed, after the above code is run, all of the formulas in the total cells, H33, H66 etc, get changed to be absolute references of the being the sum of every cell above the cell in column H. I remember I originally coded this in somewhere as that is what I originally wanted but now i just want each of the total cells to be the sum of the two cells above it. In the first case, that would be H31:H32

 

[Michael M]

Is there a way to have the $ in front and comas after each three numbers?

Umm, that's what the code does !

 

[dpaton05]

The code puts the number in the right spot but it doesn't have a dollar sign in the front of it and doesn't use any commas either.

 

[Michael M]

Did you clear the footer first ?
It works fine for me !
How is the cell formatted that provides the value ??