[VBA] Copy Range from Excel Paste into Word Autofit Contents to Window & Page & Keep Source Format

[Seba Robles]

The title pretty much sums up what I am trying to accomplish but have had no luck whatsoever.

I'm trying to copy a range from an excel worksheet to word but when I do, it changes the text formatting and the table extends beyond the first page (both to the right and onto the next page).

So I'm trying to;

- autofit contents to window
- keep source fromatting when pasting
- maintin the pasted table within one page
- paste new table onto a new blank page

Sub ExcelWordPaste()

Dim objWord As Object
Dim objDoc As Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.documents.Add
    
    Range("A9:H48").Copy
    objWord.Selection.PasteSpecial Placement:=wdInLine
    objWord.Selection.TypeParagraph

End Sub

Any help is appreciated. Thanks in advanced!

 

[Macropod]

Try:

Sub ExcelWordPaste()

Dim objWord As Object
Dim objDoc As Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
    
Range("A9:H48").Copy

Set objDoc = objWord.documents.Add
With objDoc.Range
    .PasteExcelTable False, False, False
    .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
    .InsertAfter vbCr
End With
End Sub

Do note that:
- autofit contents to window
- keep source fromatting when pasting
is contradictory. Whether you can achieve
- maintain the pasted table within one page
depends on how high the table is.

 

[Seba Robles]

So what I meant when keeping source formatting was more for font & font size. For some reason when I copy paste from Excel to Word with VBA it changes the text to Calibri size 11 I think... but the table in Excel has headers etc and each header vs content has different font sizes.

As for your last point, the table isn't very long so they fit in 1 page, but after pasting from Excel to Word, it extends it for some reason. I need to manually adjust it so that it fits in 1 page. Is there a way to do this with VBA? Similar to AutoFit to window but with length (top to bottom)?

And as for the code, it worked, but my full code is actually to copy/paste multiple tables and what you shared will only autofit the first table. Do you know how I could do it so that it autofits each table it pastes inWord?

For Each c In inputRange
    dvCell = c.Value
    
    Range("A9:H43").Copy  

With objDoc.Range
    .PasteExcelTable False, False, False
    .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
    .InsertAfter vbCr
End With

Next c

 

[Macropod]

I'm not seeing any font changes from what was originally in the worksheet, regardless of what the underlying fonts in the document are. The spacing changes are probably because you have a conflict between Word and Excel as to the characteristics of the 'Normal' Style. To overcome that and provide for multiple tables to be output, use:

With objDoc.Range.Characters.Last
  .PasteExcelTable False, False, False
  With .Tables(1)
    .Range.ParagraphFormat.SpaceBefore = 0
    .Range.ParagraphFormat.SpaceAfter = 0
    '.AutoFitBehavior 2 'wdAutoFitWindow
  End With
  .InsertAfter Chr(12)
End With

Note that I've commented-out AutoFitBehavior - you should test what you're getting without it. There is no vertical equivalent to AutoFitBehavior.

 

[Seba Robles]

This works pretty good, thank you!