Hi Dave

1.
sLine1 = ActiveSheet.Name
sLine2 = Range("A1").Text
..CenterHeader = _
"&""Arial,Bold""&11" & sLine1 & Chr(10) & Chr(10) & sLine2

2
You have hard-coded your array of sheet names so you can't change it.
If you want to process all worksheets simply
For each ws in activeworkbook.worksheets

Or for specific sheets perhaps maintain a list in a dynamic named range
(another subject) on a hidden sheet (these can also be updated with 'certain
worksheet formulas' if names change)

Dim vNames as variant

vNames = Application.Transpose(Range("namedrange"))
For Each sh In Worksheets(vNames)

or populate a Redim'd 1D horizontal array of names obtained from elsewhere

Regards,
Peter T

"deelee" wrote in
message ...

Morning all,

So far I have managed to insert a new worksheet from a hidden
'template'; unhide it and through a UserForm, change the name of that
worksheet; in addition I have managed to place the name of the
worksheet in a given cell (worksheets are indiviualised to staff
members and I need their name to appear for a formula to work) - so far
so good!

My problems are two fold;

1. I need the worksheet name to appear in the header when printing (the
user's name) but no matter how I try I can't get it to work, here is my
line of code:

CenterHeader = _
"&""Arial,Bold""&11Sheet name here!" & Chr(10) & "" & Chr(10) &
"&10POther text here"

This obviously places 'Sheet name here' as the first line of the header
and I need the ActiveSheet.Name to appear!

2. I collate all remaing worksheets into one called 'Master' and I
have another worksheet hidden called "Template".

Currently I use the following code to collate the remaining
worksheets:

'loop thru an array of sheets
For Each wsSrc In wb.Worksheets( _
Array("Carrie", "Dave", "Eileen", "Geraldine", "Jabeen", "Jackie",
"Julie", "Pauline", "Rebecca"))
'alt: array(2,3,4,5)

How do I add additional sheets to this when they are created i.e say
one called "Joe"

Sorry for the long post but I only need to have these cracked for my
project to work much better! (always enhancing :) )

Thank you,

Dave


--
deelee
------------------------------------------------------------------------
deelee's Profile:
http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=556391