Member Avatar for hvebsr

I want to put the path and name of a spreadsheet, the page and number of pages and the date and time into the footer(s) and display it in Times New Roman 6 pts.
I made the following macro:

Range("A1").Select
    ActiveWindow.View = xlPageLayoutView`
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftFooter = _
        "&""Times New Roman,Standaard""&6 &Z&F"
        .CenterFooter = _
        "&""Times New Roman,Standaard""&6 Page &P of &N"
        .RightFooter = _
        "&""Times New Roman,Standaard""&6 &D &T"
    End With
    Application.PrintCommunication = True
    ActiveWindow.View = xlNormalView
    Range("A1").Select

When I start the macro only a part of the centerfooter is filled.
When I start the same macro step by step everything is filled correctly.
What goes wrong?

  • 2 Contributors
  • 3 Replies
  • 319 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by hvebsr

Dani AI

Generated

Short answer: the behavior is almost always a timing/redraw issue caused by switching views or batching PageSetup changes. Stepping through the macro gives Excel time to flush queued PageSetup updates, so the footer looks correct; running it at full speed can leave the center footer partially rendered. later fixed the problem by using a minimal PageSetup assignment (no view toggles), which matches this diagnosis. ’s request for the final solution is why a small, robust pattern is useful for others.

Why it happens: PageSetup talks to the print/layout subsystem and Excel will batch or defer those calls (especially when Application.PrintCommunication is used). Changing ActiveWindow.View while updates are queued, or toggling PrintCommunication at the wrong time, can leave the on‑screen Page Layout in an intermediate state. Also check the code for stray characters (for example an accidental backtick next to a statement) that can produce unexpected behavior when the macro runs without pauses.

Practical guidance (safe, repeatable approach)

  • Avoid switching to Page Layout view inside the routine unless strictly necessary.
  • If batching many PageSetup changes, set PrintCommunication = False, do all assignments, then set it True — but do not change the view while PrintCommunication is False.
  • If the layout still appears wrong, force a short redraw: use DoEvents or a brief Application.Wait after restoring PrintCommunication, or toggle view off and back on with brief pauses.
  • Avoid Select/Activate; reference the sheet/page setup directly.

Robust pattern (example)

Sub SetFooters_Robust()
  Dim sh As Worksheet: Set sh = ActiveSheet
  Dim wasPLView As Boolean: wasPLView = (ActiveWindow.View = xlPageLayoutView)
  Application.ScreenUpdating = False
  On Error GoTo Done
  If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
  With sh.PageSetup
    .LeftFooter = ActiveWorkbook.FullName
    .CenterFooter = "Page &P of &N"
    .RightFooter = Format(Now, "yyyy-mm-dd HH:mm")
  End With
  If Val(Application.Version) >= 14 Then Application.PrintCommunication = True
  If wasPLView Then
    DoEvents
    ActiveWindow.View = xlNormalView
    DoEvents
    ActiveWindow.View = xlPageLayoutView
  End If
Done:
  Application.ScreenUpdating = True
End Sub

Note: the minimal fix that worked for was removing the view toggles/extra PrintCommunication handling and assigning PageSetup values directly; that is the simplest and often the most reliable solution.

Member Avatar for Stuugie

I don't see a problem but I don't know what you should be seeing, the following shows what I see

ExcelFooter.JPG 8.23 KB
Member Avatar for Stuugie

I just noticed this is marked solved, can you please share the solution for me and others to see in the future?

Member Avatar for hvebsr 
Sub Footer()
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Times New Roman,Standaard""&6 &Z&F"
        .CenterFooter = "&""Times New Roman,Standaard""&6 Page &P of &N"
        .RightFooter = "&""Times New Roman,Standaard""&6 &D &T"
    End With
End Sub
commented: Thank you +5
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.