It looks like you are trying to write a blog...

Long term users of Excel VBA will be familiar with slow processing of page set up if code like the following is used:

With ActiveWorksheet.PageSetup

.CenterHeader = "Title of the Worksheet"

.LeftFooter = "Today's Date"

.RightFooter = "Page &P of &N"

.PrintArea = "$A$1:$H$28"

End With

The problem with the code is that, despite the use of the With statement, each line requires a call to the printer driver, rather than the whole statement being sent once. The most common solution is to fall back on the Page.SetUp Excel 4 macro. An example of the kind of code that a programmer might use can be found here: McGimpsey & Associates : Excel : Bypassing Slow VBA PageSetup.

Changes to Excel 2010 were intended to remove the need to fall back on legacy code by switching on and off the printer communication (additions to code in blue):

Application.PrintCommunication = False

With ActiveWorksheeet.PageSetup

.CenterHeader = "Title of the Worksheet"

.LeftFooter = "Today's Date"

.RightFooter = "Page &P of &N"

.PrintArea = "$A$1:$H$28"

End With

Application.PrintCommunication = True

Further information can be found here: Microsoft Excel - Migrating Excel 4 Macros to VBA.

Unfortunately, it doesn't work as intended! Although I present the problem in terms of the footer, the same applies to the header.

The footer has three components: Left, Center and Right. If these have not yet been set the macro works as intended. However, if any of these have been previously assigned to, only the last footer section listed in the macro will have its text changed. That is, whichever is the last of LeftFooter, CenterFooter and RightFooter as listed in code order is changed and the others are left untouched.

Additionally, using the familiar "Page &P of &N" gets cut off if more than one section of the footer is assigned to.

It appears others have experienced the same problem:

• MsOf10 [Excel] Issue Setting Headers & Footers in 2010 - VBForums
• vba code not setting footer on all sheets - MrExcel Message Board
• Excel Macro will not change header information - Microsoft Answers

In each case the advice is not to use PrintCommunication!

This is a real pity as apart from problems with (re-) setting the footers and headers, it works exactly as intended. Nonetheless, I am still using Excel 4 macros in version 14 >.<