Adding an Image to the Header or Footer of Every Sheet

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub MultipleHeader()
'created 10/01/2008 by Angela Wolff
Application.ScreenUpdating = False 'stops the flashing
  Dim WS_Count As Integer
   Dim I As Integer

   ' Set WS_Count equal to the number of worksheets in the active workbook.
  WS_Count = ActiveWorkbook.Worksheets.Count
   
   ' Begin loop.
  For I = 1 To WS_Count
      ActiveWorkbook.Worksheets(I).Activate
      ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "C:\Document\hhnt_007528.jpg"  'obviously change this to the image and path of your desire.
   ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
    End With
   Next I
End Sub

NB. The With End With code is only really required if you are going to change a number of parameters at once.

You may also like