Help with a macro, please
 

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

Help with a macro, please
 

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


--
HTH...

Jim Thomlinson


"Bill Allen" wrote:

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

Help with a macro, please
 

Worked like a charm.

Thanks, Jim.

Bill

"Jim Thomlinson" wrote in message
...
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


--
HTH...

Jim Thomlinson


"Bill Allen" wrote:

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