VBA & Macro Problems

Fit an Excel Range on a Word Page Using VBA

Olivia MillerOlivia Miller Nov 14, 2026 868 views

Question details

Fit a range copied from Excel into the usable page width of a Word document using VBA.

Fit an Excel Range on a Word Page Using VBA
Product
Microsoft Excel and Microsoft Word
Device & OS
not provided
Scenario
A VBA procedure copies a range such as A1:E46 from Excel and pastes it into a Word document.
Observed behavior
The pasted table can exceed the Word page width, and changing Excel margins does not change the Word document's page layout.
Before you start

Confirm that your existing macro has a Word document object named wdDoc and that the pasted range becomes a Word table. The Excel source can be an ordinary cell range; it does not have to be an Excel table.

Solution 1Recommended

Set Word page layout and autofit the pasted table with VBA

Use this approach after your existing macro pastes the Excel range as a Word table.

1
Open the existing macro

In Excel, press Alt+F11 to open the VBA editor, then open the procedure that copies the range and pastes it into Word.

2
Add Word layout code

Immediately after the paste operation, add the following code. It sets A4 landscape layout, adjusts Word margins, and fits the first Word table to the available width: With wdDoc.PageSetup .PaperSize = 7 ' wdPaperA4 .Orientation = 1 ' wdOrientLandscape .TopMargin = wdDoc.Application.CentimetersToPoints(1.8) .BottomMargin = wdDoc.Application.CentimetersToPoints(1.8) .LeftMargin = wdDoc.Application.CentimetersToPoints(0.5) .RightMargin = wdDoc.Application.CentimetersToPoints(0.5) End With If wdDoc.Tables.Count > 0 Then With wdDoc.Tables(1) .Range.Font.Size = 9 .AutoFitBehavior 2 ' wdAutoFitWindow End With End If

3
Run and check the document

Run the macro from the VBA editor and inspect the Word document in Print Layout or File > Print. If the pasted table is not the first table, change Tables(1) to the appropriate table index.

Set Word page layout and autofit the pasted table with VBA
Check both width and height: AutoFit to Window targets the available page width; it does not guarantee that all rows fit on one page. If the table spans pages vertically, reduce the font size or row spacing, or adjust the Word page layout further.
Free Microsoft Office alternative

Work with Excel ranges and Word documents in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and documents. Open common Microsoft Office formats to make migration easier, and use document layout tools to adjust pasted content. It does not directly fix Microsoft Excel-to-Word VBA automation.

Adjust page orientation and margins when laying out pasted spreadsheet content.Open and edit common Microsoft Excel and Word formats, including XLSX and DOCX.Use a familiar interface for everyday spreadsheet and document tasks.Move existing Office files into a lightweight, free office suite.
QA img-9

Frequently Asked Questions

Does the Excel range need to be formatted as a table?

No. An ordinary range such as A1:E46 can be copied. The VBA formatting shown applies after the pasted content becomes a Word table.

Why don't Excel margins change the pasted table's Word layout?

Excel margins control Excel's print layout, not the Word document. Set orientation and margins through wdDoc.PageSetup or Word's Layout tab.

What if the pasted range is a picture instead of a Word table?

Word table properties will not apply to a picture. Change your macro's paste method so the content is pasted as a Word table, or resize the picture in Word instead.

Why does the table still span more than one page?

AutoFitBehavior 2 fits the table to the available width, not the page height. Check File > Print and try a smaller font, less row spacing, or different Word margins if the rows extend onto another page.