Excel 2010 .CenterFooter = "Page &P of &N"

[PWD]

If I step through the following code (F8) it works, apart from this odd message when it gets to the ".FitToPagesWide = 1"

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N[]"
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&D"
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False

    End With
    Application.PrintCommunication = True

Say OK to this message & it carries on OK.

"The number must be between 10 and 400. Try again by entering a number in this range."

BUT, if I just try to run it (F5) All I get on the page is

Page 1 of

in the centre, and nothing else!!

I have seen another post about there being an issue with Excel 2010, but I'd like to figure this out.

Many thanks,
D€$

 

[SkipVought]

hi,

        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = [b][s][highlight]False[/highlight][/s][/b]

You need a VALID numeric value [highlight] here[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Sorry Skip, but it's perfectly legal to set FitToPagesTall and/or FitToPagesWide to False when you don't want to scale ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

As you can see, Excel printing has a limited range of scalling (10%-400%), whatever scalling option you choose.
I did tests in excel 2003. When I tried to set a big table to fit to one page, it ended up with 10% of scalling (option not selected, but the displayed scalling adapts to settings), page setup set to fit one page and still several pages in print preview.
It seems that the zoom testing was removed in newer excels, but limits remained.


combo

 

[PWD]

Morning guys. I think (I hope) I can live with the 'error' issue; it's the fact that if I have this running as part of other code, it fails to set the footers correctly from this bit of the code:-

.CenterFooter = "Page &P of &N"        
.RightFooter = "&D"

The result is as if I'd only coded:-

        .CenterFooter = "Page &P of "

As I said, I've seen somewhere else that this appears to be an Excel 2010 'thing', but it's all very strange. I especially don't get why it should appear to give the desired outcome when I F8 through each line as opposed to just trying to run in - either in a stand-alone sub or as part of another sub.

I know it's my own fault for trying to enhance the output - as it wasn't part of the original spec - but I'm sure I'll need this solution in the future!

Many thanks,
D€$

 

[PWD]

Well, oddly enough it does work on a new workbook so I've decided to move it around in my code & see if that makes any difference. Previously it sat inbetween

    Sheets(ws.Name).Copy

and

    ActiveWorkbook.SaveAs filename:=MyPath & "\XYZ\" & ws.Name & UpdateDate & ".xls", FileFormat:=56

Many thanks,
D€$

 

[PWD]

Well, that made no difference. The only way I can get it to "do what is says on the tin" is to put a pause in the code & F8 through the

With ActiveSheet.PageSetup

lines.

Many thanks,
D€$