Having Issues With Page Footer When Recording A Macro

[TkdKidSnake]

Hi all,

I having issues with recording a macro to insert a page footer that shows "Page 1 of 9" for example, after recording the code looks correct however all I am getting is "Page 1" only - has anyone else had this issue and how have you managed to overcome it?

The code I can see isCode:

.CenterFooter = "&""-,Bold Italic""Page &P Of &N"[/COLOR]

Any help on this would be greatly appreciated, I'm sure its something very simple that I just can't see.


Thanks in advance

 

[jkpieterse]

Odd, it works fine for me. Can you upload a sample file exhibiting the problem. It may contain nonsense data.

 

[steve the fish]

Works for me too. You can also do it like this. Doubt it would make any difference but never know:

.CenterFooter = "&B&IPage &P of &N"

 

[TkdKidSnake]

Odd, it works fine for me. Can you upload a sample file exhibiting the problem. It may contain nonsense data.

Thank you for taking a look into this for me

The original code is in another spreadsheet and because of the issue and your statement above I have made this the last action that is performed / I recorded - the full code is below

Application.PrintCommunication = False    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$4"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$B:$U"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.748031496062992)
        .RightMargin = Application.InchesToPoints(0.748031496062992)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.984251968503937)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.511811023622047)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintSheetEnd
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .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

This is everything that is generated when recording the simple task of adding the footer.

It seems fine when I create the code all seems to have worked ok however when I remove the page footer to test the macro I get "Page 1 of"

 

[TkdKidSnake]

Works for me too. You can also do it like this. Doubt it would make any difference but never know:

.CenterFooter = "&B&IPage &P of &N"

It does seem very strange, when use the code above all i'm getting now is "Page"

 

[TkdKidSnake]

I have just stumbled across an answers but not sure what I am doing is right - however the result is

when recording the macro the code enters lines as per below
Application.PrintCommunication = False & Application.PrintCommunication = True

If I block out/negate these lines everything works fine - just in case anyone else is looking for a work around on this issue

Hi all,

I having issues with recording a macro to insert a page footer that shows "Page 1 of 9" for example, after recording the code looks correct however all I am getting is "Page 1" only - has anyone else had this issue and how have you managed to overcome it?

The code I can see isCode:

.CenterFooter = "&""-,Bold Italic""Page &P Of &N"

Any help on this would be greatly appreciated, I'm sure its something very simple that I just can't see.


Thanks in advance

 

[jkpieterse]

What of you remove all page setup stuff and replace with this:

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .CenterFooter = "Page &P of &N"
    End With
    Application.PrintCommunication = True

 

[TkdKidSnake]

I tried with just this and still ended up with the same end product - what I found was if I blocked out the Application.PrintCommunication instructions it worked fine.

What of you remove all page setup stuff and replace with this:

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .CenterFooter = "Page &P of &N"
    End With
    Application.PrintCommunication = True

 

[jkpieterse]

Odd. Glad you were able to solve it though.