I was struggling with this same bug and found your thorough summary. I have seen all of the weird behaviours that have been described, including the "step-through". It's a shame that Microsoft has not yet provided a fix and that we need to develop a workaround for a new feature.
Since the bug only seems to affect the header and footer definitions, I decided to go with a compromise - sacrificing some of the speed for accuracy in the header/footers. I only turn off PrintCommunication for non- header/footer page setups, and then turn it back on before setting the headers/footers.
The following code seems to work as intended: With ActiveSheet.PageSetup
' setup all non- header/footer properties with print
' communications suspended to speed page setup
Application.PrintCommunication = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 1
' setup all header/footer properties after reinstating
' printer communications
Application.PrintCommunication = True
.LeftHeader = "This is the Left Header"
.CenterHeader = "This is the Center Header"
.RightHeader = "This is the Right Header"
.LeftFooter = "&F[&A]"
.CenterFooter = "p. &P of &N"
.RightFooter = "printed: &D &T"
I tried to include all of the properties that are covered in some of the posted XL4 solutions. There may be other properties that should be changed along with the headers/footers.
+1 for Notepad++ here, awesome for code writing I find too
Vi, its the only way..................
Ahhh Emacs, the king of text editors, is there nothing it can't do?
I set "Application.PrintCommunication = True" and that seems to solve the problem but I have not tested it extensively i.e. turning off print communication seems to create the problem.
Originally Posted by kobusb I experienced the same problem. Interestingly it does not occur if I slowly step through the code. If I quickly step through the code then it does not properly write the footers or headers. Intriguing! Thanks for sharing this.
It makes me wonder whether there is a way to mimic this effect in the code. (My first thought was to try something like DoEvents, but that doesn't work.)
I experienced the same problem. Interestingly it does not occur if I slowly step through the code. If I quickly step through the code then it does not properly write the footers or headers.