Page &P of &N doesn't give expected result

[PWD]

Good afternoon. Just a follow-up on my previously posted question really. Why should the following code only produce the expected result if I insert a Breakpoint & step (F8) through the code but not if I just let it run as part of the rest of the code?

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&D"
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True

Many thanks,
D€$

 

[SkipVought]

hi,

Should we just guess at 1) the actual result and 2) the expected result and 3) the structure of the data on the sheet you are printing, 4) the margins, 5) the printer driver, 6) the range of data, uh what else???

Skip,

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

 

[UnsolvedCoding]

Try this -

.CenterFooter = Chr(38) & Chr(34) & "Calibri" & Chr(34) & Chr(38) & "11" & "Page &P of &N"


You will need to change the font and font size from Calibri 11 to whatever you are using.

 

[drlex]

Have you tried it after commenting out the lines "Application.PrintCommunication = False" and "Application.PrintCommunication = True"?

soi là, soi carré

 

[PWD]

Skip. Harsh! (It's probably my own fault for having 5 minutes at the end of the day & rushing out these questions.)

If I just open a new workbook, 'Book1.xlsm' and put numbers from cells A1 to P37 then run this code:-

Sub TestDesFooters()
    Application.PrintCommunication = False

    With ActiveSheet.PageSetup
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&D"
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = ""
      End With
    Application.PrintCommunication = True
End Sub

All I get in the Footer is:-

Center section:
Page &[Page] of

Having done that and then just step (F8) throught the same code on the same sheet without saving it, I only get:-

Right section:
&[Date]

If I open another new workbook & just F8 through the code, it's all as I want and I get:-

Center section:
Page &[Page] of &[Pages]
Right section:
&[Date]

If I omit,

Application.PrintCommunication = False

The I get an error 1004 message:-
"Unable to set the FitToPagesTall property of the PageSetup class"

Although it doesn't if I change the line to:-

        .FitToPagesTall = False

I don't know if margins or printer driver make any significant difference. All I know is what I've put above. F8 = OK, F5 isn't.

Many thanks,
D€$

 

[drlex]

With the Application.PrintCommunication still commented out, can you add in

.Zoom = False

?

soi là, soi carré

 

[combo]

How many printed pages do you get when you set zoom to 10% (esp. horizontal direction)?
You should stay with [tt].FitToPagesTall = False[/tt], it represents blank number in page setup.

combo

 

[PWD]

Curiously, after adding the .zoom, it appears to work just fine:-

Sub TestDesFooters()

    With ActiveSheet.PageSetup
        .Zoom = False
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&D"
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = ""
      End With

End Sub

It's curiously slow - about 5 seconds (this test sheet is still small "A1" to "P44")!! - but, hey!!

Thanks all round

Many thanks,
D€$

 

[SkipVought]

on your first code, I needed to add the zoom and change the FitToPagesTall to false

        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False

Having done so, I get the Page n of m either running or stepping.

Skip,

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

 

[PWD]

Yes indeed Skip. Result!!

Many thanks,
D€$