VBA code to add a line immediately before a footer.

[Holmely]

I want to add a line, lh margin to rh margin, immediately before the footer. Can someone help me, please, with the VBA code to do this. This is my VBA code to add a footer to a worksheet.

Sub AddFooter()
Dim Response As Integer
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.LeftFooter = "&8&F"
.CenterFooter = "&8&A"
Response = MsgBox("Do you want to maintain today's date as static ? ", vbYesNo _
+ vbQuestion)
If Response = vbYes Then
.RightFooter = "&8 " & Format(Now, "dd/mm/yy")
Else
.RightFooter = "&8" & "&D"
End If
End With
Application.ScreenUpdating = True
End Sub

[snb]

Please use code tags !

Sub Macro1()
    With ActiveSheet.Shapes.AddConnector(1, Cells(75, 1).Left, Cells(75, 1).Top + Cells(75, 1).Height - 2, Cells(75, 9).Left + Cells(75, 9).Width, Cells(75, 1).Top + Cells(75, 1).Height - 2)
        .Visible = msoTrue
        .Line.Weight = 4.5
    End With
End Sub

[Holmely]

Thank you for your quick response. Unfortunately it does not appear to work as expected. Paper size was set to A5 landscape and it printed out 4 sheets of paper. First sheet did not show line/border, 2nd sheet showed only footer (no worksheet data), 3rd showed a thick greyed line in the correct position (just above the footer) but no worksheet data, 4th sheet showed only footer with a thin greyed border 1/3rd way down the worksheet with no worksheet data. Would it be better to select the last row of a worksheet and use a xlbottomedge border to it? Could you please advise on the code and where in my macro to put it. (Sorry about the code tags! - I'm a newbie and don't know what these are.) Much appreciate your assistance.