header formatting

[Guest]

I create a header in a work book using

With ActiveSheet.PageSetup
.RightHeader = "&B" & ActiveWorkbook.Name & "&B" & Chr(13) & "&A" &
Chr(13) & "&D" & Chr(13) & "Page &P of &N"
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
ActiveWindow.Zoom = 75
End With

This macro is in file xxx. When the user runs this macro and saves the file
to yyy.xls. I want the first line of the header to be yyy.xls. Currently if
the user runs the macro and saves the file to a different name. The header
is xxx.xls.

What am I missing?
Thanks for any help
Mike

 

[Guest]

Well I have been reading and this seems to be an issue. On
http://www.j-walk.com/ss/excel/tips/tip22.htm It says that the filename will
not be changed. I am try to use a work around to set the headers before
printing and before save or save as.

The before print works well and the before save is working but is there a
before saveas?

Code below

Tahnks
Mike


Here is what I have:

Function FixHeaders()
Dim wkbk As Workbook
Dim wksht As Worksheet

Set wkbk = ActiveWorkbook
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
sht.Activate
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = "&B" & ActiveWorkbook.Name & "&B" & Chr(13) & "&A" &
Chr(13) & "&D" & Chr(13) & "Page &P of &N"
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
ActiveWindow.Zoom = 75
End With
Next sht
End Function
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call FixHeaders
End Sub



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call FixHeaders
End Sub