header macro won't update

Question

I'm using Excel 2007 and it's a large file with 15 worksheets.  Data goes in one worksheet and a report is produced from a later worksheet. I use the system for many iterations.  Works fine (if tediously slowly) but this macro is stalling.

I wanted a macro to add the name of the individual, and the name of the area in the 'header', plus some text, naming the report.  I'm not new to programming but am new to VBA, so with help from web gurus put together this code.  It worked initially as a macro - which should update with any information on worksheet 'Instructions' cells A4 and A6 before printing:

Private Sub Workbook_BeforePrint(Cancel As Boolean)            Dim ws As Worksheet            For Each ws In ActiveWorkbook.Worksheets           ws.PageSetup.RightHeader = "&I" & "&08" & Format(Worksheets("Instructions").Range("a4").Value & vbCr & Worksheets("Instructions").Range("a6").Value & vbCr & "Report 2012 ")         Next ws         End Sub

The problem is that although the macro runs, it doesn't update with the font information, which I copied and pasted then altered, to the font I wanted; and it doesn't update the values in cells A4 and A6 with new information when I 'save as' the file for the next iteration.

How do I fix this, please so that the macro runs correctly with the new font info and new saved info in those cells. 

Many thanks.

Answer 1

Make sure that the folder containing the workbook is a trusted location for Excel - see Create, remove, or change a trusted location for your files.

Answer 2

Are you sure macros are enabled? The code should run fully automatically each time the workbook is printed or print previewed; you shouldn't have to run any code manually.

Answer 3

Thanks.

Yes, that was where the code was posted. 

The code works fine - until I change the information in the source cell(s), after which it doesn't reflect the change(s) in the header it then prints.  I need it to read the latest information in those cells.  It's as if I have to re-run the macro and tell it to update, but I haven't found out how.

Answer 4

I'm guessing you want this in italic???   I've recorded a macro to get this, so hopefully it's what you want. 

"&""+,Italic""&08"

If that doesn't work, try putting this outside your For/Next loop

myHeader = "&I" & "&08" & Format(Worksheets("Instructions").Range("a4").Value & vbCr & Worksheets("Instructions").Range("a6").Value & vbCr & "Report 2012 ")

And tell us what myHeader equals.   I'm not clear on why you have format in there without an obvious format.

Answer 5

The code that you posted will run each time you print (or print preview) the workbook - if you copied the code into the ThisWorkbook module. The code won't do anything if you placed it in a standard module.

You can open the ThisWorkbook module by double-clicking on ThisWorkbook (under Microsoft Excel Objects) in the so-called Project Explorer on the left hand side of the Visual Basic Editor.