Updating Header Information via VBA

[Brock]

Hi:

I'm trying to update all the worksheet headers in a big SS at once via
VBA. I want to pull a value (a name) from a cell and have it update
the worksheet headers but I'm having problems with the following
argument:

..CenterHeader = "&""Microsoft Sans Serif,Regular""&14" &
..Range("name").Value

The entire command is as follows:

Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.LeftHeader = "&""Microsoft Sans Serif,Regular""&14" &
"Test Header"
.CenterHeader = "&""Microsoft Sans Serif,Regular""&14" &
..Range("name").Value
.RightHeader = "&""Microsoft Sans Serif,Regular""&14" &
"&D"
.CenterFooter = "&""Microsoft Sans Serif,Regular""&10" &
"Page &"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub

I've also tried:

..CenterHeader = "&""Microsoft Sans Serif,Regular""&14" & "f13" where
f13 is the cell with the name listed in it).

Any help would be appreciated.

 

[Vasant Nanavati]

It should work just fine. What specific "problems" are you having with it?

 

[Tom Ogilvy]

Does every sheet have a range named "name" - a worksheet level name. If
not, you should not qualify it with the worksheet name



.CenterHeader = "&""MS Sans Serif,Regular""&14" & Range("Name").Value

or at the start

sName = Range("Name").Value

Loop . . .

.CenterHeader = "&""MS Sans Serif,Regular""&14" & sName

End of loop . . .

Otherwise, describe what problem are you having.

 

[Guest]

try adding a blank before Range("name").valu

..CenterHeader = "&""Microsoft Sans Serif,Regular""&14" & " " & Range("name").Valu

 

[wbcarter]

I am trying to do the same thing. Here is what I want.

When Text is entered in worksheet 1 A1, I want the text entered int
.CenterHearder of worksheet 3 and 4.


Thanks for the hel

 

[wbcarter]

I got this to work. The the benefit of the community, here is the code
After the formatting I had to enter a & and variable without quotes.

Sub ClientName()
Application.ScreenUpdating = False
Dim cName As String
cName = Worksheets("Summary").Range("A1").Value
'Declare string variable for Client Name from Summary Page A1
'
With Worksheets(3).PageSetup
.CenterHeader = "&""Arial,Bold""&12" & cName
"&""Arial,Regular""&10" & Chr(10) & "&""Arial,Bold""&A"
End With
Application.ScreenUpdating = True

End Su