Is it possible to have a Month formula in a custom header, so that as the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell, and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since others
will be using the worksheet and don't want to have to change the header
themselves.

You could have a dedicated macro that you run once a month to update the
header--or you could change the header each time you open the file (or even each
time you print the file).

This changes the header each time the workbook is opened--whether it needs it or
not:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks.PageSetup
.CenterHeader = Format(Date, "mmmm")
End With
End Sub

I'd record a macro that formatted that header the exact way that I want it. Use
XXXXX as the month name and replace "XXXXX" in the recorded code with
Format(date,"mmmm").

If you need help post back with your current code.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dar wrote:

Is it possible to have a Month formula in a custom header, so that as the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell, and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since others
will be using the worksheet and don't want to have to change the header
themselves.

--

Dave Peterson

Put this in the ThisWorkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterHeader = Format(Date, "mmmm")
End With
End Sub


--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
Is it possible to have a Month formula in a custom header, so that as the
new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell, and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since others
will be using the worksheet and don't want to have to change the header
themselves.

That worked perfectly. Now one last thing. I have 12 other tabs
(worksheets) that the macro should cycle through. Can you add the code for
that.
I know it should be something that has
For Each sh............
Thanks.

"Dave Peterson" wrote:

You could have a dedicated macro that you run once a month to update the
header--or you could change the header each time you open the file (or even each
time you print the file).

This changes the header each time the workbook is opened--whether it needs it or
not:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks.PageSetup
.CenterHeader = Format(Date, "mmmm")
End With
End Sub

I'd record a macro that formatted that header the exact way that I want it. Use
XXXXX as the month name and replace "XXXXX" in the recorded code with
Format(date,"mmmm").

If you need help post back with your current code.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dar wrote:

Is it possible to have a Month formula in a custom header, so that as the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell, and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since others
will be using the worksheet and don't want to have to change the header
themselves.

--

Dave Peterson

The one I presented will do it for all sheets everytime you print.

--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
That worked perfectly. Now one last thing. I have 12 other tabs
(worksheets) that the macro should cycle through. Can you add the code
for
that.
I know it should be something that has
For Each sh............
Thanks.

"Dave Peterson" wrote:

You could have a dedicated macro that you run once a month to update the
header--or you could change the header each time you open the file (or
even each
time you print the file).

This changes the header each time the workbook is opened--whether it
needs it or
not:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks.PageSetup
.CenterHeader = Format(Date, "mmmm")
End With
End Sub

I'd record a macro that formatted that header the exact way that I want
it. Use
XXXXX as the month name and replace "XXXXX" in the recorded code with
Format(date,"mmmm").

If you need help post back with your current code.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dar wrote:

Is it possible to have a Month formula in a custom header, so that as
the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell,
and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since
others
will be using the worksheet and don't want to have to change the header
themselves.

--

Dave Peterson

I have named the worksheets (FA2E), (FA2W), etc. When I leave the macro as
is (Sheet1) I get an error, subscript out of range. When I change Sheet1 to
FA2E, which happens to be sheet1, it works.

"Don Guillett" wrote:


The one I presented will do it for all sheets everytime you print.

--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
That worked perfectly. Now one last thing. I have 12 other tabs
(worksheets) that the macro should cycle through. Can you add the code
for
that.
I know it should be something that has
For Each sh............
Thanks.

"Dave Peterson" wrote:

You could have a dedicated macro that you run once a month to update the
header--or you could change the header each time you open the file (or
even each
time you print the file).

This changes the header each time the workbook is opened--whether it
needs it or
not:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks.PageSetup
.CenterHeader = Format(Date, "mmmm")
End With
End Sub

I'd record a macro that formatted that header the exact way that I want
it. Use
XXXXX as the month name and replace "XXXXX" in the recorded code with
Format(date,"mmmm").

If you need help post back with your current code.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dar wrote:

Is it possible to have a Month formula in a custom header, so that as
the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell,
and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since
others
will be using the worksheet and don't want to have to change the header
themselves.

--

Dave Peterson

sheet1
or
sheets("fa2e")

--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
I have named the worksheets (FA2E), (FA2W), etc. When I leave the macro as
is (Sheet1) I get an error, subscript out of range. When I change Sheet1
to
FA2E, which happens to be sheet1, it works.

"Don Guillett" wrote:


The one I presented will do it for all sheets everytime you print.

--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
That worked perfectly. Now one last thing. I have 12 other tabs
(worksheets) that the macro should cycle through. Can you add the code
for
that.
I know it should be something that has
For Each sh............
Thanks.

"Dave Peterson" wrote:

You could have a dedicated macro that you run once a month to update
the
header--or you could change the header each time you open the file (or
even each
time you print the file).

This changes the header each time the workbook is opened--whether it
needs it or
not:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks.PageSetup
.CenterHeader = Format(Date, "mmmm")
End With
End Sub

I'd record a macro that formatted that header the exact way that I
want
it. Use
XXXXX as the month name and replace "XXXXX" in the recorded code with
Format(date,"mmmm").

If you need help post back with your current code.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dar wrote:

Is it possible to have a Month formula in a custom header, so that
as
the new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a
cell,
and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since
others
will be using the worksheet and don't want to have to change the
header
themselves.

--

Dave Peterson

This works great. Hats off to you.

"Don Guillett" wrote:

Put this in the ThisWorkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterHeader = Format(Date, "mmmm")
End With
End Sub


--
Don Guillett
SalesAid Software

"Dar" wrote in message
...
Is it possible to have a Month formula in a custom header, so that as the
new
month begins, the header will display the Name of the Month.
Example, =Text(Today(),"mmmm") will show December right now in a cell, and
when January arrives, will show January.
But I want somehow to have the Month show up in the header, since others
will be using the worksheet and don't want to have to change the header
themselves.