Copy & Paste pictures from Excel into Header (and Footer) of Word

Hi there,

Having earlier today (!) mastered a VBA for creating a new Word 2010 document and copying and pasting data from Excel 2010, adjusting margins and saving the doc as a filename determined by an Excel Cell, I now need to copy and paste data - a picture to be precise into a Header within Word - from Excel (and then the same thing for a Footer).

My picture is derived from a named cell (Letter_Logo) at present that refers to an offset formula: =OFFSET(Logos!$B$1,'SL Int'!$A$5,0) - (where Logos! and 'SL Int'! are the names of two separate worksheets)

Any ideas?

My existing VBA for all the earlier stuff is:


Sub COPY_FINAL_SL()
'
' COPY_FINAL_SL Macro
' COPY FINAL SL Copy column A; Open and Paste into Word; Set margins to Narrow and save as Cell B7 then reset to B1.
'
Dim number As Integer, i As Integer, WS As Worksheet, number_exported As Integer
Dim wdApp As Object, wdDoc As Object, MyWd As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
Set WS = ActiveSheet
Range("A:A").Copy

wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

' Margins Macro
' SET MARGINS TO NARROW
'
wdDoc.PageSetup.LineNumbering.Active = False
wdDoc.PageSetup.Orientation = wdOrientPortrait
wdDoc.PageSetup.TopMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.BottomMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.LeftMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.RightMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.Gutter = MillimetersToPoints(0)
wdDoc.PageSetup.HeaderDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.FooterDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.PageWidth = MillimetersToPoints(210)
wdDoc.PageSetup.PageHeight = MillimetersToPoints(297)
wdDoc.PageSetup.FirstPageTray = wdPrinterDefaultBin
wdDoc.PageSetup.OtherPagesTray = wdPrinterDefaultBin
wdDoc.PageSetup.SectionStart = wdSectionNewPage
wdDoc.PageSetup.OddAndEvenPagesHeaderFooter = False
wdDoc.PageSetup.DifferentFirstPageHeaderFooter = False
wdDoc.PageSetup.VerticalAlignment = wdAlignVerticalTop
wdDoc.PageSetup.SuppressEndnotes = False
wdDoc.PageSetup.MirrorMargins = False
wdDoc.PageSetup.TwoPagesOnOne = False
wdDoc.PageSetup.BookFoldPrinting = False
wdDoc.PageSetup.BookFoldRevPrinting = False
wdDoc.PageSetup.BookFoldPrintingSheets = 1
wdDoc.PageSetup.GutterPos = wdGutterPosLeft

wdApp.Visible = True

wdApp.ActiveDocument.SaveAs Range("B7").Value


Set wdApp = Nothing

Range("B1").Select

End Sub

Any thoughts? - or perhaps more simply how to just paste a picture from Excel into a Word header and I'll try and work out the rest myself??