Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Header Information via VBA
try adding a blank before Range("name").valu ..CenterHeader = "&""Microsoft Sans Serif,Regular""&14" & " " & Range("name").Valu |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Header Information | Excel Worksheet Functions | |||
Updating Information in all worksheet. | Excel Discussion (Misc queries) | |||
Excel Updating of information | Excel Worksheet Functions | |||
Updating the date in Custom Header | Excel Discussion (Misc queries) | |||
How do I Freeze information to keep from updating | Excel Programming |