Copy Paste from Excel to specified word document

[Guest]

Ok. I've got the below code and it seems to work fine all the way up to where
it's going to paste into word (last sub) and it has an error. I want it to
paste the copied cells in to word (as unformatted text). It keeps throwing up
an error runtime error 1004. Any ideas?



Sub CopyCellsFromExcel()

Dim wordApp As Object
Dim fNameAndPath As String
Dim wdPastetext As String

ActiveSheet.Range("I5:I49").Copy
Application.CutCopyMode = False

Sub OpenAWordFile()


fNameAndPath = "C:\Documents and Settings\tom.jordan\My
Documents\Projects\FFEC Headed Paper.doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True

Selection.PasteSpecial DataType:=wdPastetext

End Sub

 

[JW]

You've got a couple of things going on here. One, you are using
variables in both subs but only have them declared in
CopyCellsFromExcel. While that may work if you don't have Option
Explicit stated, it is not good coding practice. Second, you have two
Sub start lines but only one End Sub. Third, you have
Application.CutCopyMode set to false right after you perform the cut.
That line will remove anything you have stored on teh clipboard, so
there would be nothing to place in Word. I combined the two subs, but
you could always break them apart if you need to.
Sub OpenAWordFile()
Dim wordApp As Object
Dim fNameAndPath As String
ActiveSheet.Range("A1:A9").Copy
fNameAndPath = "C:\Documents and Settings\tom.jordan\My" & _
"Documents\Projects\FFEC Headed Paper.doc"
Set wordApp = CreateObject("Word.Application")
With wordApp
.Documents.Open (fNameAndPath)
.Visible = True
.Selection.PasteAndFormat (wdPasteDefault)
End With
Set wordApp = Nothing
Application.CutCopyMode = False
End Sub

 

[Guest]

JW

Oops. I hadn't actually had two subs as I had merged them into one but
forgot to take out the second sub declaration out of this post. I've put in
your code into my excel sheet and it works. However, it pastes the table and
everything. I need this particular macro to paste the excel group into Word
as unformatted text i.e. it looks like normal text, no tables or anything.

Any idea how to do that?