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]

Yeah, we kinda jump the gun sometimes !
Make notes of what you want to chieve...THEN post the question in full.
Keep in mimd witht code I posted it will probably need to be cut and pasted to the macro that holds the variables (lr & ref3)

 

[dpaton05]

Might have been helpful if you provide sufficient information in the first post !!
UNTESTED, as I don't have the last row in H or the ref3 variable

Sub MM1()
With ActiveSheet.PageSetup
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.CenterFooter.Text = ""
    .LeftFooter = Format(Now(), "mmmm d, yyyy")
    .CenterFooter = ref3 & " " & Chr(10) & .Range("H" & lr).Value
End With
End Sub

In response to your code Michael, I am putting the code in my procedure that has ref3 but for the lr, I need to sum every cell in column H that is 1 column to the right of the text, "Total incl. GST". There could be only 1 instance of this phrase or there could be multiple instances. Could you help me with the code please?

 

[Michael M]

So, if you want the last crow in "H" use col "G" as your reference, as it contains the text you mention

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
With ActiveSheet.PageSetup
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.CenterFooter.Text = ""
    .LeftFooter = Format(Now(), "mmmm d, yyyy")
    .CenterFooter = ref3 & " " & Chr(10) & .Range("H" & lr).Value
End With
End Sub

 

[dpaton05]

Do I need to include some code to search for the text Total incl. GST?

I also need to sum all of the instances if there are more than 1.

 

[dpaton05]

By multiple instances, I mean that everything between A1:V33 relates to one period. The entire range can be copied below for displaying information in regards to a new period. There will be totals for each period in column H and I want a grand total in the footer of all the periods, which will be a sum of all the cells I described in post #12.

 

[Michael M]

I don't have Excel at the moment, so UNTESTED, Someone else might jump in and assist
BUT this is a bit of a stretch from the initial question !!!

Sub MM1()
Dim lr As Long, r As Range, s As Long, c As Range
lr = Cells(Rows.Count, "H").End(xlUp).Row
s = 1
For Each r In Range("H1:H" & lr)
    If r.Offset(0, -1).Value = "Total incl. GST" Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
        End With
    End If
Next r
With ActiveSheet.PageSetup
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.CenterFooter.Text = ""
    .LeftFooter = Format(Now(), "mmmm d, yyyy")
    .CenterFooter = ref3 & " " & Chr(10) & .Range("H" & lr).Value
End With
End Sub

 

[dpaton05]

Thanks for that Michael. I get the error with that code of object doesn't support this property of method and the last line of code is highlighted.

 

[dpaton05]

I also have defined ref3 in the code before that line of code is run.

 

[Michael M]

Change to

Sub MM1()
Dim lr As Long, r As Range, s As Long, c As Range
lr = Cells(Rows.Count, "H").End(xlUp).Row
s = 1
For Each r In Range("H1:H" & lr)
    If r.Offset(0, -1).Value = "Total incl. GST" Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
        End With
    End If
Next r
With ActiveSheet.PageSetup
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.CenterFooter.Text = ""
    .LeftFooter = Format(Now(), "mmmm d, yyyy")
    .CenterFooter = ref3 & " " & Chr(10) & Range("H" & lr).Value
End With
End Sub

 

[dpaton05]

Thank you so much Michael. That was giving me quite a bit of anxiety as I wasn't sure how I could do that. Now I just need it to sum the totals in the footer as it doesn't seem to add them together.