Excel Macro to add "Page n of n"

[Barry Flynn]

Hi

I am trying to create a little macro that will create a Page Header,
containing "page n of n".
I have used "record a macro" to create the macro.

But, when I run the resulting macro, it gives an error "unable to set the
leftheader property of the pagesetup class."

What am I doing wrong?

The macro is pasted below.

Thanks

Barry

-------------------------------------------------------------------------------------------------

Sub PageHeader()
'
' PageHeader Macro
'

'
Range("A1").Select
ActiveWindow.View = xlPageLayoutView
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Page &P of &N" & Chr(10) & ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Zoom = 100
.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
Range("A1:B1").Select
ActiveWindow.View = xlNormalView
End Sub

 

[Barry Flynn]

I forgot to mention the version - it is Excel 2007

Barry

 

[Jim Rech]

Your macro ran fine for me so I don't know why you got a macro error.

--
Jim
"Barry Flynn" <Anonymouse> wrote in message
| Hi
|
| I am trying to create a little macro that will create a Page Header,
| containing "page n of n".
| I have used "record a macro" to create the macro.
|
| But, when I run the resulting macro, it gives an error "unable to set the
| leftheader property of the pagesetup class."
|
| What am I doing wrong?
|
| The macro is pasted below.
|
| Thanks
|
| Barry
|
| -------------------------------------------------------------------------------------------------
|
| Sub PageHeader()
| '
| ' PageHeader Macro
| '
|
| '
| Range("A1").Select
| ActiveWindow.View = xlPageLayoutView
| With ActiveSheet.PageSetup
| .LeftHeader = ""
| .CenterHeader = "Page &P of &N" & Chr(10) & ""
| .RightHeader = ""
| .LeftFooter = ""
| .CenterFooter = ""
| .RightFooter = ""
| .LeftMargin = Application.InchesToPoints(0.7)
| .RightMargin = Application.InchesToPoints(0.7)
| .TopMargin = Application.InchesToPoints(0.75)
| .BottomMargin = Application.InchesToPoints(0.75)
| .HeaderMargin = Application.InchesToPoints(0.3)
| .FooterMargin = Application.InchesToPoints(0.3)
| .Zoom = 100
| .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
| Range("A1:B1").Select
| ActiveWindow.View = xlNormalView
| End Sub
|
|

 

[Barry Flynn]

Jim

Thanks for responding.
I have since found the answer (I guess I should have researched a bit more
before I posted the question) - it is because the PC that I was running in
did not have a default printer

It does seem curious to me though that I could perform the keystrokes to
create the macro with no problem.
The lack of a default printer didn't seem to matter while I was doing that.
But it mattered when I tried to run the macro.

Thanks

Barry