Avatar of jrogersok
 asked on

Excel 2010 Footer Macro - Footer Doesn't Display properly in Print Preview

I recorded an Excel macro that places a footer with date/time on the left, Page x of y on the right and when the file is previewed either the data is garbeled on the left or it says "Page 1 of" on the right (without the total pages)

This same macro has been recorded in Office 2007 without any issues.  Any one know the "fix" for this?


File is attached with a preview of the garbled that is showing on the right and code is also attached. Recorded macro
Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D &T"
        .CenterFooter = ""
        .RightFooter = "Page &P of &N"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True

Open in new window

Microsoft ExcelSpreadsheetsVB Script

Avatar of undefined
Last Comment
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

8/22/2022 - Mon
Swapnil Nirmal

Try recording the macro and use the line that you get

i have got one line if it could help

.RightFooter = "&""Times New Roman,Bold Italic""Page &P of &N"

Tried your code and still didn't work -- the issue seems to be with it not understanding the code recorded or entered.  Perhaps a Service Pack fixes this?  We do not have any installed at the company's site.

See image below Screen shot 2
Swapnil Nirmal

see if this helps
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Swapnil Nirmal

hey got something, try this:

.RightFooter = "Page &P of &N"

Nope -- didn't work.  

I am teaching a class of vba students and they are all having the issue so i'm sure it's something with the recorder/objectlibrary in Excel itself. Thanks for the tips.

Would like a permanent solution rather than a work-a-round (although if I could get a work around, at least I could share it)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)


there is a bug in Excel 2010 with regards to some of the footer codes. For the ones that don't work, append a set of square brackets, like this

.RightFooter = "page &P of &N[]"

In my trials, the &P worked fine, but the &N did not. Adding the [] after &N did the trick. But when I added ...

.LeftFooter ="page &P"

... to the code, I had to use...

.LeftFooter ="page &P[]"
.RightFooter = "page &P of &N[]"

... to make the whole parcel work.

So, it also seems to depend on whether or not there's stuff in the left footer, and what that stuff is. Just play around with adding the square brackets.

IMPORTANT: Before you run the macro, manually clear existing footers, because they will not always be overwritten and results may look funny.

Cheers, teylyn
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.