Help with a macro, please

[Bill Allen]

I'm a neophyte when it comes to writing macros, so please forgive my
ignorance.

I assemble workbooks based on templates I've created over time. Of course,
as time passes, each template evolves. One of the problems I have is getting
the page numbers to work correctly. I place them in a header so that they
will print in a particular location in my spreadsheet. Of course, this
location has moved over time (slightly, one or two characters to the left or
right) and it's been tedious to edit these manually in a spreadsheet that
has several (50-100) worksheets. The page numbers also move depending if the
total number of pages is less than 10, less than 100 or more than 100.

What I did was enter macro record mode, create a macro and edit it to suite.
Here is the copy of the code:

Sub PageNumberPlus()



' Keyboard Shortcut: Ctrl+Shift+P



With ActiveSheet.PageSetup



.RightHeader = _

"&""EurostileExtended-Roman-DTC,Bold""&8 &P
&N"

End With

End Sub



This works great. All I have to do is to edit the number of spaces before
"&P" and befor "&N".



Unfortunately, I have to select a single worksheet before executing the
macro. What I would like to to is to select a group of worksheets and have
the macro cycle on every worksheet that is selected.



How would I modify the code to make this work?



Thanks,



Bill Allen

 

[Guest]

Give this a try... It cycles through the selected sheets...

Sub PageNumberPlus()
' Keyboard Shortcut: Ctrl+Shift+P
Dim wks as worksheet

for each wks in Activewindow.selectedsheets
With wks.PageSetup
.RightHeader = _
"&""EurostileExtended-Roman-DTC,Bold""& 8 &P &N"
End With
next wks

End Sub

 

[Bill Allen]

Worked like a charm.

Thanks, Jim.

Bill

Give this a try... It cycles through the selected sheets...

Sub PageNumberPlus()
' Keyboard Shortcut: Ctrl+Shift+P
Dim wks as worksheet

for each wks in Activewindow.selectedsheets
With wks.PageSetup
.RightHeader = _
"&""EurostileExtended-Roman-DTC,Bold""& 8 &P &N"
End With
next wks

End Sub