Hard code month in Excel

[Rachel Costanza]

I have a excel workbook with 15 worksheets and every month when I produce the
annualized turnover I have to go into each header on each sheet to update the
month. Do you know of a way using VB or a DATE FUNCTION I can automate the
template to have the current month in the header? For example: Turnover
October 2008

Best,

Rachel

 

[FiluDlidu]

Hi Rachel,

Would this suits your needs?

Sub AddMonth()
For i = 1 To Sheets.Count
Sheets(i).Select
Range("A1").Formula = "=today()+30"
Range("A1").Value = Range("A1").Value
Range("A1").NumberFormat = "mmmm yyyy"
Range("A1").Select 'Not necessary, but you might like it...
Next i
Sheets(1).Select 'Not necessary, but again, you may find it useful
End Sub

Regards,
Feelu

 

[FiluDlidu]

Of course I meant

Sub AddMonth()
For i = 1 To Sheets.Count
Sheets(i).Select
Range("A1").Formula = "=today()"
Range("A1").Value = Range("A1").Value
Range("A1").NumberFormat = "mmmm yyyy"
Range("A1").Select 'Not necessary, but you might like it...
Next i
Sheets(1).Select 'Not necessary, but again, you may find it useful
End Sub

Sorry for carelessness...

 

[FiluDlidu]

OK... Take 3:

Sub AddMonth()
For i = 1 To Sheets.Count
Sheets(i).Select
Range("A1").Formula = "=today()"
Range("A1").Value = Range("A1").Value
Range("A1").NumberFormat = "mmmm yyyy"
Range("A1").Select 'Not necessary, but you might like it...
Next i
Sheets(1).Select 'Not necessary, but again, you may find it useful
End Sub

 

[Sheeloo]

if you want the current month on EACH sheet... then use
=Text(today(),"mmm")

This month it will show Nov but next month it will show Dec...

If you want it in Header under PAGE SETUP, then you need a macro...similar
to the one below;

Sub UpdateHeader()
For Each ws In Worksheets
ws.PageSetup.LeftHeader = Date
Next
End Sub

 

[MyVeryOwnSelf]

I have a excel workbook with 15 worksheets and every month when I

produce the annualized turnover I have to go into each header on each
sheet to update the month. Do you know of a way using VB or a DATE
FUNCTION I can automate the template to have the current month in the
header? For example: Turnover October 2008

If by "header" you mean the first row of the worksheet, you can choose one
sheet where the current month is entered, and have formulas in all the
others that refer to that one.

On the other hand, if the "header" is what's set up using
File > Page setup > Header/Footer
then you can select all worksheets at once beforehand, and the update will
apply to all the worksheets. One way to do this is to right-click on one of
the tabs (at the bottom of the Excel window) and choose "Select All
Sheets."

(I have Excel 2003.)

 

[MyVeryOwnSelf]

I have a excel workbook with 15 worksheets and every month when I

produce the annualized turnover I have to go into each header on each
sheet to update the month. Do you know of a way using VB or a DATE
FUNCTION I can automate the template to have the current month in the
header? For example: Turnover October 2008

If by "header" you mean the first row of the worksheet, you can choose one
sheet where the current month is entered, and have formulas in all the
others that refer to that one.

On the other hand, if the "header" is what's set up using
File > Page setup > Header/Footer
then you can select all worksheets at once beforehand, and the update will
apply to all the worksheets. One way to do this is to right-click on one of
the tabs (at the bottom of the Excel window) and choose "Select All
Sheets."

(I have Excel 2003.)