ExcelBanter - using excel macro to activate word document

If the Word macro is saved as "Macro1" in a template named MyTemplate.dot
and the range I want to copy over is A1:B2, the following would work (in
addition to editing to the range you really want, correct the path info for
templates on your PC)

Sub XLtoWord()
Range("A1:B2").Select
Selection.Copy

Dim bStarted As Boolean
Dim oApp As Object
Dim strPath As String

strPath = "C:\Documents and Settings\your ID\"
strPath = strPath & "Application Data\Microsoft\Templates\"

On Error Resume Next
Set oApp = GetObject(, "Word.Application")

If Err < 0 Then
bStarted = True
Set oApp = CreateObject("Word.Application")
End If

oApp.Activate
oApp.Visible = True
oApp.Documents.Add Template:=strPath & "MyTemplate.dot"
oApp.Selection.Paste

oApp.Run "Macro1"

If bStarted Then
oApp.Quit
End If

Set oApp = Nothing
End Sub

Steve

"Amy" wrote in message
...
I have a bunch of data in Excel that I would like to copy
and paste into a Word document to create different
tables. I have written a macro in Word to format these
tables once the excel data is copied over.

Is there a way I can use an Excel macro to automatically
select a block of cells, copy and paste into Word, and
activate the Word macro?

Thanks,

Amy