Updating Header Information via VBA
 

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.

Updating Header Information via VBA
 

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

--

Vasant

"Brock" wrote in message
om...
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.

Updating Header Information via VBA
 

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.

--
Regards,
Tom Ogilvy


"Brock" wrote in message
om...
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.

Updating Header Information via VBA
 

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

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

Updating Header Information via VBA
 

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

--
Message posted from http://www.ExcelForum.com

Updating Header Information via VBA
 

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

--
Message posted from http://www.ExcelForum.com