# Excel VBA sheet to word autofit



## excel_learnerz (Jan 3, 2019)

Hello
With the below code I can export used ranged as table into Microsoft word. 
When it goes to word the table isn't autofitted, so half of it is not appearing correctly.
Is there a way for it to be formatted correctly in the word document
Thanks in advance


Sub annex()
With Sheets("Sheet1").Select
    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:="C:\temp" & ActiveSheet.Name
End With
End Sub


----------



## Macropod (Jan 3, 2019)

excel_learnerz said:


> Hello
> With the below code I can export used ranged as table into Microsoft word.
> When it goes to word the table isn't autofitted, so half of it is not appearing correctly.
> Is there a way for it to be formatted correctly in the word document


That all depends on what you mean by 'formatted correctly'. Try one of:
newObj.Range.PasteExcelTable False, False, False
newObj.Range.PasteAndFormat 16 'wdFormatOriginalFormatting
newObj.Range.PasteAndFormat 19 'wdUseDestinationStylesRecovery


----------

