Create Header In Word Doc From Excel VBA

[Logit]

.
I'm using this following macro to create a report in WORD. After FILTERING the data in the EXCEL sheet to obtain the report results I'm seeking, this macro copies that filtered data to a WORD doc in landscape mode.

I want to include at the top of the report the following string : "Report Run On : " & Now

The macro copies the filtered data from row 10 to the bottom then pastes that data into the WORD doc. I'm open to a completely different macro if necessary.

Spent most of today researching the FORUM and the NET for answers. It would appear the issue may be centered on the method used to copy/paste the data in the WORD doc as most all of the examples reviewed
create the report differently. Never worked with WORD before so I'm feeling slightly out of water here.

Anyone have a suggestion ? Thank you so much !

Option Explicit

Sub CopyFilteredToWord()
Dim FirstCell As Range, LastCell As Range
Dim wdApp As Object, WdDoc As Object

On Error Resume Next

Set wdApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
    Set wdApp = CreateObject("Word.Application")
End If

On Error GoTo 0

Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
  Cells.Find(What:="*", SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Range("A10", LastCell).Copy

wdApp.Visible = True

Set WdDoc = wdApp.Documents.Add

'  "Report Run On :" & Now

With WdDoc

  .Range.PasteExcelTable False, False, False
  .Tables(1).PreferredWidthType = 1 'wdPreferredWidthAuto
  .PageSetup.Orientation = 1
End With

Application.CutCopyMode = False
Range("A8").Select

End Sub

[sktneer]

You may give this a try...

Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
  Cells.Find(What:="*", SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Range("A10", LastCell).Copy

With wdApp
    .Visible = True
    .Activate
    Set WdDoc = .Documents.Add
    With .Selection
        .ParagraphFormat.Alignment = 1
        .BoldRun
        .Font.Size = 16
        .TypeText "Report Run On :" & Now
        .BoldRun
        .TypeParagraph
        .ParagraphFormat.Alignment = 0
        .Paste
    End With
End With

WdDoc.Tables(1).PreferredWidthType = 1
WdDoc.PageSetup.Orientation = 1

[macropod]

You don't need any code for this. All you need to do is to specify an appropriate template via:
Set WdDoc = wdApp.Documents.Add
and, before use, insert 'Report Run On : ' followed by a CREATEDATE field into the page header of that template.

[Logit]

sktneer : Thank you. Precisely what I was seeking.

macropod : I am not familiar with creating templates for WORD but would be thankful if you could provide an example. One confusion I have is how would this be implemented into my project ? What I perceive is my macro creates a
'spur of the moment' construction of data and simply pastes that info into a Word doc. How would I get the filtered data into a Word template ? I'm probably over thinking the process.

[sktneer]

You're welcome Logit! Glad it worked as desired.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.

[macropod]

A template is simply a basic document you save as one (i.e. using the dotx or dotm format, as appropriate). When you create a new document and base it on a template, you'll be adding your filtered data to the document, not to the template, just as you're doing now; in fact, your failure to specify a template via Documents.Add merely results in Word creating a new document based on its 'Normal' template.