Updating Headers/Footers in VB

[Andrew P]

Morning

I am updating a whole workbook of Headers and Footers using the following:

Public Sub UPDATE_HEADER_FOOTER()
Dim mysheet As Worksheet
For Each mysheet In ActiveWorkbook.Sheets
With mysheet.PageSetup
.RightFooter = "Issued by Central Personnel - July 28th 2006"
.RightHeader = "&""Arial,Bold""&11Reporting Period 4" & vbCr & _
"4 Weeks to 16 July 2006"

End With
Next
End Sub


However when the Macro hits a Sheet containing a Chart (Fill page) it stops and doesn't continue with the rest of the book. (i do want the charts to have a H and F as well).

Error Type mismatch (Error 13)

Please could anyone drop me a hint.?

thanks for your help.

[Hayeso]

The problem that you have is that the Chart sheets are members of the Sheets
collection but are not Worksheets. If you declare mysheet as a variant then
it will work

Dim Mysheet

instead of

Dim Mysheet as Worksheet


"Andrew P" wrote:

>
> Morning
>
> I am updating a whole workbook of Headers and Footers using the
> following:
>
> Public Sub UPDATE_HEADER_FOOTER()
> Dim mysheet As Worksheet
> For Each mysheet In ActiveWorkbook.Sheets
> With mysheet.PageSetup
> .RightFooter = "Issued by Central Personnel - July 28th 2006"
> .RightHeader = "&""Arial,Bold""&11Reporting Period 4" & vbCr & _
> "4 Weeks to 16 July 2006"
>
> End With
> Next
> End Sub
>
>
> However when the Macro hits a Sheet containing a Chart (Fill page) it
> stops and doesn't continue with the rest of the book. (i do want the
> charts to have a H and F as well).
>
> ERROR TYPE MISMATCH (ERROR 13)
>
> Please could anyone drop me a hint.?
>
> thanks for your help.
>
>
> --
> Andrew P
> ------------------------------------------------------------------------
> Andrew P's Profile: http://www.excelforum.com/member.php...o&userid=36574
> View this thread: http://www.excelforum.com/showthread...hreadid=563206
>
>