Question with existing page numbering macro?
 

Hi the code below is an abstract from a macro I use to automatically page
numbers when printing,

Sub workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
..PageSetup.CenterFooter = "" & .Range("A1").Text
End With

With Worksheets("sheet2")
..PageSetup.CenterFooter = "" & .Range("a1").Text
End With

'etc etc

End Sub

The macro functions as required and is placed within Thisworkbook.

As the number of pages have grown to 20, the macro will only function if I
press print preview before printing, otherwise the page numbering is not
updated. I'm guessing the print function is quicker than the macro and it
doesn't get chance to complete.

Not the end of the world, but I just wondered if there was a way to get
around this to allolw the macro enough time to complete before the printing
starts?


--
This post was created using recycled electrons!

Question with existing page numbering macro?
 

I don't think it is a question of time, I think it is more a matter of the
page numbers are calculated dynamically, when a print (or Printpreview)
command is issued. I have often noticed in a large Word document if you do a
Page n of m in the footer, as you scroll down, m is updated as well as n,
Word works it out.

I haven't tried this, and it maybe overkill, but perhaps you could try
forcing a printpreview.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
With Worksheets("sheet1")
Application.EnableEvents = False
.PrintPreview
.PageSetup.CenterFooter = "" & .Range("A1").Text
.PrintOut
Application.EnableEvents = True
End With
End Sub

Of course the problem here is that you get a preview that you need to
respond to.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Newbeetle" wrote in message
...
Hi the code below is an abstract from a macro I use to automatically page
numbers when printing,

Sub workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.CenterFooter = "" & .Range("A1").Text
End With

With Worksheets("sheet2")
.PageSetup.CenterFooter = "" & .Range("a1").Text
End With

'etc etc

End Sub

The macro functions as required and is placed within Thisworkbook.

As the number of pages have grown to 20, the macro will only function if I
press print preview before printing, otherwise the page numbering is not
updated. I'm guessing the print function is quicker than the macro and it
doesn't get chance to complete.

Not the end of the world, but I just wondered if there was a way to get
around this to allolw the macro enough time to complete before the
printing
starts?


--
This post was created using recycled electrons!

Question with existing page numbering macro?
 

Hi Newbeetle,

Try suspending recalcs and using a loop, like:
Sub workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
' Stop Re-Calcs
Application.Calculation = xlManual
' Loop through all sheets
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.PageSetup.CenterFooter = "" & wsSheet.Range("A1").Text
Next wsSheet
' Restore Re-Calcs
Application.Calculation = xlAutomatic
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"Newbeetle" wrote in message ...
| Hi the code below is an abstract from a macro I use to automatically page
| numbers when printing,
|
| Sub workbook_BeforePrint(Cancel As Boolean)
| With Worksheets("sheet1")
| .PageSetup.CenterFooter = "" & .Range("A1").Text
| End With
|
| With Worksheets("sheet2")
| .PageSetup.CenterFooter = "" & .Range("a1").Text
| End With
|
| 'etc etc
|
| End Sub
|
| The macro functions as required and is placed within Thisworkbook.
|
| As the number of pages have grown to 20, the macro will only function if I
| press print preview before printing, otherwise the page numbering is not
| updated. I'm guessing the print function is quicker than the macro and it
| doesn't get chance to complete.
|
| Not the end of the world, but I just wondered if there was a way to get
| around this to allolw the macro enough time to complete before the printing
| starts?
|
|
| --
| This post was created using recycled electrons!

Question with existing page numbering macro?
 

Hi Guys thanks for the help,

Bob I tried this, funny thing is when the print preview happens this way it
still has the wrong page numbers, if I use the print preview button built in
to excel it updates ok ready to be printed.

Macropod, that looping macro certainly has cut the script down, as you could
expect the way I was doing the code meant for 20 pages it was very long, so
thats a major help. Weird thing again though pages still don't show the
correct numbering unless I do manual print preview first.

Well its not a major thing, I put a note on it for the other users to print
preview first, thanks for the help and advice, both are useful for other
things I'm playing around with, and it keeps me thinking.



--
This post was created using recycled electrons!


"Newbeetle" wrote:

Hi the code below is an abstract from a macro I use to automatically page
numbers when printing,

Sub workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.CenterFooter = "" & .Range("A1").Text
End With

With Worksheets("sheet2")
.PageSetup.CenterFooter = "" & .Range("a1").Text
End With

'etc etc

End Sub

The macro functions as required and is placed within Thisworkbook.

As the number of pages have grown to 20, the macro will only function if I
press print preview before printing, otherwise the page numbering is not
updated. I'm guessing the print function is quicker than the macro and it
doesn't get chance to complete.

Not the end of the world, but I just wondered if there was a way to get
around this to allolw the macro enough time to complete before the printing
starts?


--
This post was created using recycled electrons!