Clicky

Use vba to customise excel sheet footer
 

Hi
I need to modify the page numbers shown in the footer of a spreadsheet using vba

I usually set the footers thus

For each oSheet in thisworkbook.worksheets
with osheet.pagesetup

 .RightFooter = "&P of &N"

End With
Next osheet

Lets say my print has 2 pages
I want page 1 to show as "2  of 3"


If a sheet has 6 pages
I want page 4 to show as "5  of 7"

And so on for all pages

eg
The total to increase by 1 above the actual total
and the actual page number to increase by 1 more than the actual page number

I tried
        .RightFooter = "&P+1 of &N+1"

That added 1 to the actal page number -great! :-)
But it appended a 1 onto the total number of pages (eg. 6 pages total but returned "61") -not so great :-/

can anyone help solve what I think should be a simple question

asked 12/15/2011 07:21

sirplus's gravatar image

sirplus ♦♦


3 Answers:
Hi, sirplus.

Add a space at the end of your string...

I've no idea why it works or how consistent it would be for you, but it worked for me in Excel 2010.

Regards,
Brian.
link

answered

redmondb's gravatar image

redmondb

Seems to work
IDKW?............
link

answered 2011-12-15 at 20:24:48

sirplus's gravatar image

sirplus

Beats me, sirplus. I keep an eye on "my" closed questions for a couple of weeks so please post here if it starts to misbehave.

Thanks,
Brian.
link

answered 2011-12-15 at 21:59:40

redmondb's gravatar image

redmondb

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×89
×19
×83

Asked: 12/15/2011 07:21

Seen: 422 times

Last updated: 12/15/2011 02:00