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
Bookmarks