Code for page footer

[Hayton]

Hi Guys,

We have in the centre pace of our footer the following text "© Companies Name 2006".

When we roll over from one year to another we have to open all our workbooks and change the year say from 2005 to 2006.

Is there some VBA I could insert that will change the year automatically as we move from one year to the next. This will have to be for all worksheets in a workbook.

Any ideas will be appreciated.

Hayton McGregor

 

[Gavona]

In Excel:
ActiveSheet.PageSetup.CenterFooter = MyText + Year(Now())
should help
but questions remain:
how to trigger this on the documents of concern but not on other documents
how to avoid overwriting a legitimate centre footer

....doh! just realised - you may only want to do this for new documents? in which case you just change the relevant document templates. Best tell us which application!


Gavin

 

[Hayton]

Hi Gavona,

Thanks for the reply.

This is the code that I have tried:
Private Sub Workbook_Open()
' forces workbook to open at worksheet named Dashboard
' and insert current year into cetre footer.

Worksheets("Dashboard").Activate
Worksheets("Dashboard").PageSetup.CenterFooter = "&""Tahoma,Regular""&8 + Year(Now())"

End Sub

The year(now)) is not working. It pastes + year(now()) into the footer and not the year 2006.

Any ideas on how to make it work.

Cheers

Hayton McGregor

 

[fumei]

That is because you have Year(Now() within quotation marks. Therefore it is going in as a string. Take it out from between the " ".

Gerry

 

[Hayton]

Thanks the guidance guys it is now working.

Hayton McGregor