Header and Footer

This example prints the workbook name and page number at the bottom of each page.
Worksheets("Sheet1").PageSetup.CenterFooter = "&F page &P"

This example prints the date and page number at the top of each page.
Worksheets("Sheet1").PageSetup.CenterHeader = "&D page &P of &N"

Header and Footer - Graphic Object

Contains properties that apply to header and footer picture objects.

Using the Graphic object
There are several properties of the PageSetup object that return the Graphic object.

Use the CenterFooterPicture, CenterHeaderPicture, LeftFooterPicture, LeftHeaderPicture, RightFooterPicture, or RightHeaderPicture properties to return a Graphic object.

CenterFooterPicture - Returns a Graphic object that represents the picture for the center section of the footer. Used to set attributes about the picture.
The CenterFooterPicture property is read-only, but the properties on it are not all read-only.

The following example adds a picture titled: Sample.jpg from the C:\ drive to the left section of the footer. This example assumes that a file called Sample.jpg exists on the C:\ drive.

Sub InsertPicture() 

    With ActiveSheet.PageSetup.LeftFooterPicture 
        .FileName = "C:\Sample.jpg" 
        .Height = 275.25 
        .Width = 463.5 
        .Brightness = 0.36 
        .ColorType = msoPictureGrayscale 
        .Contrast = 0.39 
        .CropBottom = -14.4 
        .CropLeft = -28.8 
        .CropRight = -14.4 
        .CropTop = 21.6 
    End With 

    ' Enable the image to show up in the left footer.
    ActiveSheet.PageSetup.LeftFooter = "&G" 

End Sub 

Note It is required that "&G" is a part of the LeftFooter string in order for the image to show up in the left footer.