VBA paste special to Word

I'm trying to paste paragraphs from Excel to Word. I want to be able to paste "keep text only" so long sentences would naturally go to a second line instead of disappearing in Word. I have tried to modify the paste statement with no lucks. Thank you for your help.

Sub export_excel_to_word()
Set obj = CreateObject("Word.Application")
obj.Visible =True
Set newObj = obj.Documents.Add
ActiveSheet.UsedRange.Copy
newObj.Range.Paste
Application.CutCopyMode =False
obj.Activate
newObj.SaveAs Filename:=Application.ActiveWorkbook.Path &""& ActiveSheet.Name
EndSub

Cross-posted at:
https://www.mrexcel.com/forum/genera...size-word.html

and:
https://stackoverflow.com/questions/...-word-document

Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

Try this it works for me.

Code:

---

Sub PasteUsedRangeToWordPasteTextOnly()
Set obj = CreateObject("Word.Application")
obj.Visible = True
Set newobj = obj.Documents.Add
ActiveSheet.UsedRange.Copy
obj.Activate
newobj.Range.PasteSpecial DataType:=wdPasteText
End Sub

---

The word document ends up with extra spaces so I usually run this as well:

Code:

---

Sub RemoveSpaceTabReplaceWithTab()
Selection.WholeStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = vbTab
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "^32{2,}"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub]

---

Sorry here is a better functioning bit to fix up the word document. It gets rid of the table and replaces the tabs with spaces.

Code:

---

Sub FixPaste()
Selection.WholeStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Tables(1).Select
Selection.Copy
Selection.Tables(1).Select
Selection.Tables(1).Delete
Selection.PasteAndFormat (wdFormatPlainText)
With Selection.Find
.Text = vbTab
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "^32{2,}"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub

---

Kilroy: According to the OP the question was answered a week ago @ https://www.mrexcel.com/forum/genera...size-word.html. You might want to check the code there.

Thanks Paul. I'll check it out. I know the above code is a little wonky but I ask for help so I try to help when I can.