VBA & Macro Problems

How to Create an Excel Button to Export Worksheet Data to Word

Camila Milosovich Sep 30, 2026 868 views

Question details

The user wants to set up a button in Excel that triggers a VBA macro to automatically collect worksheet answers and export them into a Word document.

Create an Excel Button to Export Worksheet Data to Word

Product: Excel
Device & OS: not provided
Scenario: Streamlining data transfer from an Excel form or worksheet to a Word document so that users do not have to manually copy and paste the information.
Observed behavior: Without a macro, users must manually copy worksheet data and paste it into Word, which is time-consuming and error-prone for unfamiliar users.

Before you start

Ensure that macros are enabled in your Excel application and that you have desktop versions of both Excel and Word installed. You will also need the Developer tab enabled on your ribbon.

Solution 1Recommended

Write a VBA Macro and Assign it to a Form Control Button

This is the primary method for automating data transfer. You will write a VBA script that opens Word, pastes the data, and then link that script to a clickable button.

To achieve this, you need to use VBA (Visual Basic for Applications) to create a bridge between Excel and Word. The macro will define the specific cell ranges to copy, launch the Word application, open a new document or a predefined template, and paste the values.

Enable the Developer Tab

Right-click anywhere on the Excel ribbon and select 'Customize the Ribbon'. In the right pane, check the box next to 'Developer' and click OK.

Create the VBA Macro

Go to the Developer tab and click 'Visual Basic' (or press ALT + F11). Go to Insert > Module, and write the VBA code that creates a 'Word.Application' object, copies your specific Excel range, and pastes it into the Word document.

Insert a Form Control Button

Return to your Excel worksheet. On the Developer tab, click 'Insert' and choose 'Button (Form Control)' from the drop-down menu. Click and drag on your worksheet to draw the button.

Assign the Macro to the Button

As soon as you finish drawing the button, the 'Assign Macro' dialog box will appear. Select the Word export macro you just created and click OK.

Test the Export Function

Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) in a trusted location. Click the newly created button to verify that it successfully opens Word and transfers your data.

Write a VBA Macro and Assign it to a Form Control Button

Tip for Early Binding: If you are writing the VBA code yourself, go to Tools > References in the VBA Editor and check 'Microsoft Word Object Library' to enable auto-completion for Word VBA commands.

Solution 2

Seek Community Help for Custom VBA Code

Use this solution if you need help writing the specific VBA code tailored to your exact worksheet layout and Word document format.

Automate Your Workflow with WPS Office

WPS Office provides robust VBA support, allowing you to seamlessly create macros and buttons that transfer data between WPS Spreadsheet and WPS Writer. Enjoy a seamless, integrated office suite experience without the heavy subscription costs.

1. Install WPS Office: Download and install WPS Office, which includes WPS Spreadsheet, Writer, and Presentation in one unified app.
2. Open Your Workbook: Launch WPS Spreadsheet and open your data collection worksheet.
3. Access the VBA Editor: Navigate to the Developer tab, open the Visual Basic Editor, and paste your data transfer code.
4. Insert the Export Button: Use the Form Controls under the Developer tab to insert a button, assign your macro, and easily export your data to WPS Writer.

Excellent compatibility with Microsoft Excel (.xlsx, .xlsm) and Word (.docx) formatsAdvanced VBA macro and Form Control support in WPS SpreadsheetLightweight, fast performance on any deviceFree to download with a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel macro button not doing anything when clicked?

This usually happens because macros are disabled in your Trust Center settings. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings, and ensure macros are enabled. Alternatively, the button might not be correctly assigned to the macro. Right-click the button and select 'Assign Macro' to verify.

Can I export my Excel data into an existing Word template rather than a blank document?

Yes. In your VBA code, instead of using 'Documents.Add' to create a blank file, use 'Documents.Add Template:="C:\Path\To\Your\Template.dotx"' to open a new document based on your specific Word layout.

How do I fix the 'User-defined type not defined' error in my VBA code?

This error occurs if your code uses 'Early Binding' (e.g., declaring variables as Word.Application) without enabling the Word Object Library. To fix this, open the VBA Editor, go to Tools > References, and check the box for 'Microsoft Word Object Library'.

Does this macro work on Excel for Mac?

VBA macros that interact with other applications (like Word) often struggle on Excel for Mac due to macOS sandboxing rules. It is highly recommended to run cross-application VBA scripts on a Windows environment for the best compatibility.

Excel & Data Problems