How to Create VBA Code to Copy a Range from Excel to Word

VBA stands for Visual Basic for Applications, which is a programming language that allows you to automate tasks in Microsoft Office applications, such as Excel and Word. One of the common tasks that you may want to automate is copying a range of cells from Excel and pasting it into a Word document. This can be useful for creating reports, invoices, charts, or other documents that require data from Excel.

In this article, we will explain how to create VBA code to copy a range from Excel to Word, using Excel formulas and the Range object. We will also provide a comprehensive explanation of the basic theory behind the code, the procedures to follow, and a scenario to give a detailed example with real numbers. We will also show you how to use an Excel table to make the code more dynamic and flexible, and suggest other approaches that you can try.

To create VBA code to copy a range from Excel to Word, we need to use two main objects: the Range object and the Word object. The Range object represents a cell or a group of cells in Excel, and it has various properties and methods that allow us to manipulate the data in the range. The Word object represents the Word application, and it has various properties and methods that allow us to control the Word document, such as opening, saving, closing, and formatting.

The basic steps to create VBA code to copy a range from Excel to Word are:

Define the range that you want to copy in Excel, using the Range object and Excel formulas.
Create a reference to the Word application, using the Word object and the CreateObject function.
Open or create a Word document, using the Documents property and the Open or Add method of the Word object.
Paste the range into the Word document, using the Paste or PasteSpecial method of the Selection object, which represents the current selection in the Word document.
Save and close the Word document, using the Save and Close methods of the Document object, which represents the Word document that you opened or created.
Release the reference to the Word application, using the Quit method of the Word object and the Set statement.

Procedures

To create VBA code to copy a range from Excel to Word, you need to follow these procedures:

Open the Excel workbook that contains the data that you want to copy, and activate the worksheet that contains the range that you want to copy.
Press Alt + F11 to open the Visual Basic Editor, where you can write and run VBA code.
In the Project Explorer window, right-click on the name of the workbook, and select Insert > Module. This will create a new module where you can write your VBA code.
In the Code window, type or paste the following code:

Sub CopyRangeToWord()

    'Define the range that you want to copy in Excel
    Dim rng As Range
    Set rng = Range("A1:D10") 'Change this to the range that you want to copy
    
    'Create a reference to the Word application
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    
    'Open or create a Word document
    Dim wdDoc As Object
    Set wdDoc = wdApp.Documents.Open("C:\Users\user\Documents\Report.docx") 'Change this to the path and name of the Word document that you want to open or create
    
    'Paste the range into the Word document
    wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
    
    'Save and close the Word document
    wdDoc.Save
    wdDoc.Close
    
    'Release the reference to the Word application
    wdApp.Quit
    Set wdApp = Nothing
    
End Sub

Modify the code according to your needs, such as changing the range that you want to copy, the path and name of the Word document that you want to open or create, and the paste options that you want to use.
Run the code by pressing F5 or clicking the Run button on the toolbar. This will execute the code and copy the range from Excel to Word.
Check the Word document to see the result.

Explanation

The following is a comprehensive explanation of the code that we wrote in the previous section, including the basics of each line of code:

Sub CopyRangeToWord()

This line declares the name of the subroutine, which is a block of code that performs a specific task. You can name the subroutine anything you want, as long as it follows the naming rules of VBA. The Sub and End Sub keywords mark the beginning and the end of the subroutine.

    'Define the range that you want to copy in Excel
    Dim rng As Range
    Set rng = Range("A1:D10") 'Change this to the range that you want to copy

These lines define the range that you want to copy in Excel, using the Range object and Excel formulas. The Dim keyword declares a variable, which is a name that represents a value or an object. The As keyword specifies the data type of the variable, which is Range in this case. The Set keyword assigns an object to a variable, using the equal sign (=). The Range function returns a Range object that represents a cell or a group of cells in Excel, using the cell references or names as the argument. In this example, we use “A1:D10” as the argument, which means that we want to copy the range from cell A1 to cell D10. You can change this argument to any range that you want to copy, such as “B2:F15” or “SalesData”.

    'Create a reference to the Word application
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")

These lines create a reference to the Word application, using the Word object and the CreateObject function. The CreateObject function creates an instance of an object from a registered program, using the program’s class name as the argument. In this case, we use “Word.Application” as the argument, which means that we want to create an object that represents the Word application. We assign this object to the variable wdApp, which we declared as an Object data type. The Object data type can hold any kind of object, but it does not provide access to the object’s properties and methods unless you use the late binding technique, which we will explain later.

    'Open or create a Word document
    Dim wdDoc As Object
    Set wdDoc = wdApp.Documents.Open("C:\Users\user\Documents\Report.docx") 'Change this to the path and name of the Word document that you want to open or create

These lines open or create a Word document, using the Documents property and the Open or Add method of the Word object. The Documents property returns a Documents object that represents the collection of all open documents in the Word application. The Open method opens an existing document and returns a Document object that represents the opened document, using the file name as the argument. The Add method creates a new document and returns a Document object that represents the new document, using optional arguments such as the template and the document type. In this example, we use the Open method and pass the file name “C:\Users\user\Documents\Report.docx” as the argument, which means that we want to open the document named Report.docx in the specified folder. You can change this argument to any file name that you want to open or create, such as “C:\Users\user\Documents\Invoice.docx” or “C:\Users\user\Documents\Chart.doc”. We assign the returned Document object to the variable wdDoc, which we declared as an Object data type.

    'Paste the range into the Word document
    wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False

This line pastes the range into the Word document, using the Paste or PasteSpecial method of the Selection object, which represents the current selection in the Word document. The Paste method pastes the contents of the Clipboard at the insertion point, using the default paste options. The PasteSpecial method pastes the contents of the Clipboard at the insertion point, using the specified paste options, such as the link, the data type, the placement, and the display as icon. In this example, we use the PasteSpecial method and pass the following arguments:

Link:=False, which means that we do not want to create a link between the source data in Excel and the destination data in Word. If we set this argument to True, then any changes in the source data will be reflected in the destination data automatically.
DataType:=wdPasteMetafilePicture, which means that we want to paste the data as a metafile picture, which is a vector graphic format that preserves the formatting and appearance of the data. You can use other constants for this argument, such as wdPasteText, wdPasteBitmap, wdPasteRTF, and so on, depending on the format that you want to use.
Placement:=wdInLine, which means that we want to place the data in line with the text, which is the default placement option. You can use another constant for this argument, which is wdFloatOverText, which means that you want to place the data over the text, which allows you to move and resize the data as you wish.
DisplayAsIcon:=False, which means that we do not want to display the data as an icon, which is a small graphic that represents the data. If we set this argument to True, then we need to specify the icon file name, the icon label, and the icon index as additional arguments.

This argument determines whether we want to display the data as an icon or not, which is a small graphic that represents the data. If we set this argument to False, then the data will be displayed as it is. If we set this argument to True, then we need to specify the icon file name, the icon label, and the icon index as additional arguments.

    'Save and close the Word document
    wdDoc.Save
    wdDoc.Close

These lines save and close the Word document, using the Save and Close methods of the Document object, which represents the Word document that we opened or created. The Save method saves the document with the same name and format as before, or prompts the user to enter a name and format if the document is new. The Close method closes the document and releases the memory that it occupies.

    'Release the reference to the Word application
    wdApp.Quit
    Set wdApp = Nothing

These lines release the reference to the Word application, using the Quit method of the Word object and the Set statement. The Quit method quits the Word application and closes all open documents. The Set statement assigns Nothing to the variable wdApp, which means that the variable no longer holds a reference to the Word object. This is important to avoid memory leaks and errors.

Example

To illustrate how the code works, let us use a scenario where we want to copy a range of cells from Excel that contains the sales data of a company, and paste it into a Word document that contains the sales report of the company. The sales data is in the range A1:D10, and the Word document is named SalesReport.docx. The following table shows the sales data in Excel:

Table

Product	Price	Quantity	Revenue
A	$10	100	$1,000
B	$15	80	$1,200
C	$20	60	$1,200
D	$25	50	$1,250
E	$30	40	$1,200
F	$35	30	$1,050
G	$40	20	$800
H	$45	10	$450
I	$50	5	$250
J	$55	2	$110

To copy the range from Excel to Word, we can use the code that we wrote in the previous section, with the following modifications:

Change the range argument to “A1:D10”, which is the range that contains the sales data.
Change the file name argument to “C:\Users\user\Documents\SalesReport.docx”, which is the path and name of the Word document that contains the sales report.

The modified code is:

Sub CopyRangeToWord()

    'Define the range that you want to copy in Excel
    Dim rng As Range
    Set rng = Range("A1:D10") 'Change this to the range that you want to copy
    
    'Create a reference to the Word application
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    
    'Open or create a Word document
    Dim wdDoc As Object
    Set wdDoc = wdApp.Documents.Open("C:\Users\user\Documents\SalesReport.docx") 'Change this to the path and name of the Word document that you want to open or create
    
    'Paste the range into the Word document
    wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
    
    'Save and close the Word document
    wdDoc.Save
    wdDoc.Close
    
    'Release the reference to the Word application
    wdApp.Quit
    Set wdApp = Nothing
    
End Sub

After running the code, the Word document will look like this:

!Word document after pasting the range

As you can see, the range from Excel is pasted into the Word document as a metafile picture, which preserves the formatting and appearance of the data. You can also resize and move the picture as you wish.

Using an Excel Table

The code that we wrote in the previous section works well if the range that we want to copy is fixed and does not change. However, if the range is dynamic and may change depending on the data, then we need to use a more flexible and robust way to define the range. One way to do that is to use an Excel table, which is a structured and organized way to store and manage data in Excel.

An Excel table has many advantages over a regular range, such as:

It automatically expands or contracts when you add or delete data, so you do not need to adjust the range manually.
It has a header row that contains the column names, which makes it easier to identify and reference the data.
It has filters and sorting options that allow you to manipulate and analyze the data quickly and easily.
It has a total row that allows you to calculate summary statistics for the data, such as sum, average, count, and so on.
It has a name that you can use to refer to the entire table or any part of it, such as a column, a row, or a cell.

To use an Excel table to copy a range from Excel to Word, you need to follow these steps:

Convert the range that you want to copy into an Excel table, using the Insert > Table command on the ribbon, or the Ctrl + T shortcut on the keyboard. This will create a table with a default name, such as Table1, Table2, and so on. You can change the name of the table by clicking on the table and using the Name Box on the left side of the formula bar, or the Design > Table Name command on the ribbon.
Modify the code that we wrote in the previous section, by changing the range argument to the name of the table, enclosed in square brackets. For example, if the name of the table is SalesData, then the range argument should be [SalesData].
Run the code and check the result.

The following image shows the Excel table that contains the sales data, with the name SalesData:

!Excel table with the name SalesData

The modified code is:

Sub CopyRangeToWord()

    'Define the range that you want to copy in Excel
    Dim rng As Range
    Set rng = Range("[SalesData]") 'Change this to the name of the table that you want to copy
    
    'Create a reference to the Word application
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    
    'Open or create a Word document
    Dim wdDoc As Object
    Set wdDoc = wdApp.Documents.Open("C:\Users\user\Documents\SalesReport.docx") 'Change this to the path and name of the Word document that you want to open or create
    
    'Paste the range into the Word document
    wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
    
    'Save and close the Word document
    wdDoc.Save
    wdDoc.Close
    
    'Release the reference to the Word application
    wdApp.Quit
    Set wdApp = Nothing
    
End Sub

After running the code, the Word document will look the same as before, but the range that is pasted will be the entire table, regardless of how many rows or columns it has. This means that if you add or delete data in the table, the code will automatically adjust the range and paste the updated data into the Word document.

Other Approaches

The approach that we used in this article is not the only way to create VBA code to copy a range from Excel to Word. There are other approaches that you can try, such as:

Using early binding instead of late binding, which means that you declare the Word object as a specific data type, such as Word.Application, instead of a generic data type, such as Object. This requires that you set a reference to the Word object library in the Tools > References command on the ribbon, and that you use the New keyword instead of the CreateObject function to create the Word object. The advantage of early binding is that it provides access to the Word object’s properties and methods at design time, which means that you can use the IntelliSense feature to autocomplete the code and avoid errors. The disadvantage of early binding is that it may cause compatibility issues if the version of the Word application is different from the version of the Word object library that you referenced.

Using the Copy method of the Range object instead of the Paste or PasteSpecial method of the Selection object, which means that you copy the range from Excel to the Clipboard, and then paste it from the Clipboard to the Word document. This requires that you use the Paste or PasteSpecial method of the Range object in the Word document, instead of the Selection object. The advantage of this approach is that it gives you more control over the destination range in the Word document, such as the location, the size, and the format. The disadvantage of this approach is that it may overwrite the existing data in the destination range, unless you specify a different paste option, such as xlPasteValues, xlPasteFormats, and so on.
Using the CopyPicture method of the Range object instead of the Paste or PasteSpecial method of the Selection object, which means that you copy the range from Excel as a picture, and then paste it as a picture to the Word document. This requires that you use the Paste or PasteSpecial method of the InlineShapes or Shapes object in the Word document, instead of the Selection object. The advantage of this approach is that it preserves the appearance of the range as a picture, which may be more suitable for some purposes, such as charts, diagrams, or logos. The disadvantage of this approach is that it loses the functionality of the range as a data source, which means that you cannot edit, update, or link the data in the picture.

These are some of the other approaches that you can try to create VBA code to copy a range from Excel to Word. You can experiment with different combinations of methods, arguments, and options to find the best solution for your needs.