How to insert the contents of two cells in a footer?

[Guest]

I want to insert the contents of 2 cells (L5 and M5) into a footer.

I am using the following: -

ActiveSheet.PageSetup.LeftFooter = Range("M5").Value

When, I change the Range command to read Range("M5,L5").Value, I just get
the contents for cell L5 printed (which by the way is a date that has been
formatted to dd/mm/yyyy).

When, I change the Range command to read Range("M5:L5").Value, I just get
the contents for cell M5 printed (which by the way is text).

When, I change the Range command to read Range("M5", "L5").Value, I just get
the contents for cell M5 printed (which by the way is text).

I also defined cells L5 and M5 as a named range (For_Week). When, I change
the Range command to read Range("For_Weekâ€).Value, I just get the contents
for cell M5 printed (which by the way is text).

I have had a look at the print preview and in the Left Footer I can see the
Text “FOR WEEK†but nothing after it.

Can you please help to solve my problem?

 

[Guest]

this worked for me to "concatenate" two cells with a space in the middle

mystring = Range("M5").Value
mytstring2 = Range("L5").Value
myfooter = mystring & " " & mystring2

 

[Guest]

Paul,

I have the following code but it only prints the date (in the format I want).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
mystring = Format(Range("M5").Value, "dd/mm/yyyy")
mytstring2 = Range("L5").Value
myfooter = mystring & " " & mystring2
Me.Unprotect
ActiveSheet.PageSetup.LeftFooter = myfooter
Me.Protect
End Sub

Any more suggestions?

Pank

 

[Dave Peterson]

You have a couple of typos:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myString As String
Dim myString2 As String
Dim myFooter As String

myString = Format(Range("M5").Value, "dd/mm/yyyy")
myString2 = Range("L5").Value
myFooter = myString & " " & myString2
Me.Unprotect
ActiveSheet.PageSetup.LeftFooter = myFooter
Me.Protect
End Sub

Sometimes you used mytstring2 and sometimes you used mystring2. If you force
yourself to declare your variables ("Option Explicit" at the top of the module),
these errors can be found much more quickly.

And I don't think you need to unprotect the sheet to change the footer--which is
a problem when you really want that footer protected!

 

[Guest]

Dave,

Once again Super Dave to the rescue, but seriously many thanks.

My thanks to Paul as well.

Regards

Pank