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?

Thanks!

File is attached with a preview of the garbled that is showing on the right and code is also attached.

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

Last Comment

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

8/22/2022 - Mon

Swapnil Nirmal

7/12/2011

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"

jrogersok

7/12/2011

ASKER

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

Swapnil Nirmal

7/12/2011

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

7/12/2011

hey got something, try this:

.RightFooter = "Page &P of &N"

jrogersok

7/12/2011

ASKER

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)

7/12/2011

Hello,

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

7/12/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

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

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.