Copy worksheet information to Word using VBA in Microsoft Excel

If you are required to copy & paste data from multiple excel worksheets into MS Word then you should read this article. In excel, there is no such feature that could allow you to convert the data from excel workbook to word file. Word application cannot open excel files directly. However, excel data can be copied & pasted into word & then saved as word document.
We all transfer data from excel to word manually which becomes tedious sometimes when it comes to performing the same steps too many times in a day; in order to avoid the manual steps, we will write VBA code to do all the steps.

In this article, we will focus on moving data from excel to word document. We will automate the whole procedure via VBA code. With macro code, you can simply copy the data in one worksheet at one time & then word application will be launched automatically & VBA code will use paste command to insert the data into doc file.


To get the code; we need to follow the below steps to launch VB editor:

  • Click on Developer tab
  • From Code group, select Visual Basic




  • There is an important step which you must not miss
  • Click on Tools to select References
  • Scroll down till you find “Microsoft Word 15.0 Object Library”
  • Make sure you check the box; refer below image




  • Copy the below code in the standard module


Sub CopyWorksheetsToWord()
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
For Each ws In ActiveWorkbook.Worksheets
    Application.StatusBar = "Copying data from " & ws.Name & "..."
    Application.CutCopyMode = False
  If Not ws.Name = Worksheets(Worksheets.Count).Name Then
        With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
            .Collapse Direction:=wdCollapseEnd
            .InsertBreak Type:=wdPageBreak
        End With
    End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
With wdApp.ActiveWindow
    If .View.SplitSpecial = wdPaneNone Then
        .ActivePane.View.Type = wdNormalView
        .View.Type = wdNormalView
    End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

image 1.1



To test the code, let us insert 2 sheets & add random numbers. Following is the snapshot of both the worksheets:

1st Sheet:-

image 2


2nd Sheet:-

image 3


  • After setting up the data in worksheets; we can run the macro
  • Word application will be launched immediately & data will be copied from all the worksheets into word; refer below snapshot

image 1


If you think copying the data is one task but there should be a page break between each worksheets data so that anyone can easily make the difference between data from each worksheet.

The above data will get copied from one sheet & then VBA code will make sure to add page break between the pages.


Conclusion: In this way, we can help lot of Microsoft Office users who think converting data from excel file into word is a bit complicated or impossible. The above code has demonstrated how easily one can copy & paste data into word file without any fuss from multiple excel sheets to word doc file.


image 48


If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at


  1. Thanks for this,

    Im trying to export date from specific cells into labels that are placed in specific fields on a table in a word document,

    Can any advise on proccess of bulding the Macro. I understand that i will have to fill in the specifics with the target names if fields ETC i just need a bit of help to get running,

    Thank you in advance 🙂

  2. Thanks for the information.
    Is there a way to Export information from Word to Excel?

    I look to compile several Word documents into an Excel file.
    Thanks and happy new year!!

  3. Hello I am trying to use this code to export data from excel to a pre-existing word table. I am a novice in this area. Pls help! Regards, Fane

    I tried to write the code, but I have a Compile error (user-defined type...):

    Sub ExportDataWordTable()

    Const stWordDocument As String = "Template fisa de esantionare var.4.docm"

    Dim wdApp As Word.Application

    Dim wdDoc As Word.Document

    Dim wdCell As Word.Cell

    Dim i As Long
    Dim j As Long
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaData As Variant

    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet1")

    ReDim vaData(1 To 10, 1 To 8)

    With wsSheet
    vaData = .Range("A2:H11")
    End With

    'Here we instantiate the new object.
    Set wdApp = New Word.Application

    'Here the target document resides in the same folder as the workbook.
    Set wdDoc = wdApp.Documents.Open(wbBook.Path & "\" & stWordDocument)

    'Import data to the first table and in the first column of a table in Microsoft Word.
    For j = 1 To 8
    i = 0

    For Each wdCell In wdDoc.Tables(1).Columns(j).Cells
    i = i + 1
    wdCell.Range.Text = vaData(i, j)
    Next wdCell

    Next j

    'Save and close the document.
    With wdDoc
    End With

    'Close the hidden instance of Microsoft Word.

    'Release the external variables from the memory
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "The data has been transferred to Template fisa de esantionare var.4", vbInformation
    End Sub?

  4. adnan saifuddin patrawala

    Dear team what if I have to past the data in existing file how can I open the existing file to past the data

  5. Ashish Badgujar

    This explanation is very helpful for copying the data from excel to word. But can you give information for how we can copy graphs from excel sheet to word?

  6. Hello All .Very useful article. Would you be able to to help me for the following : i have a text in Excel worksheet that i want to copy and paste into Excel VB Standard Module . Currently I can do it using "Cntl C" to copy and then Cntl V " to paste into Excel VB Module . Can you please suggest some other method of how it can be done without pushing the above buttons . Thank you

    • Hi Michael,

      Please use following code in module..Assuming we have code from range A1 to A5. Also, add reference by going: Tools-->References--> "Microsoft Visual Basic for Applications Extensibility".

      Sub InsertProcedureCode(ByVal wb As Workbook, ByVal InsertToModuleName As String)
      ' inserts new code in module named InsertModuleName in wb
      ' needs customizing depending on the code to insert
      Dim VBCM As CodeModule
      Dim InsertLineIndex As Long
      Dim i, lastrow As Long
      On Error Resume Next
      Set VBCM = wb.VBProject.VBComponents(InsertToModuleName).CodeModule
      lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
      If Not VBCM Is Nothing Then
          With VBCM
              InsertLineIndex = .CountOfLines + 1
              ' customize the next lines depending on the code you want to insert
              For i = 1 To lastrow
                  .InsertLines InsertLineIndex, Cells(i, 1).Value & Chr(13)
                  InsertLineIndex = InsertLineIndex + 1
          End With
          Set VBCM = Nothing
      End If
      On Error GoTo 0
      End Sub
      Sub calling_module()
          InsertProcedureCode ActiveWorkbook, "Module1"
      End Sub

      Happy Learning,
      Team Excel Tip

  7. I'm trying to create an excel macro to copy cells from excel to word. Suppose we have several pages in sheet 1 and 2 and require to copy in word. Copy of excel page by page to word. Whole sheet not in one paste as text size get reduce. May be copy according to excel page setup.

  8. Hi all, i just tried this code and receiving this error: " User_defined type not defined" can you pleas help me? I have some Auto shapes on my spreadsheets as well.

  9. Excellent and very useful. I just need to find a way of creating / formatting the new word document to A3 landscape before pasting the excel data to save more manual manipulation of the output word doc and I will be extremely happy.

  10. "I use this information.
    I dude, how to format in word the information
    to copy excel, I print and cut information.
    Regards. "

Leave a Reply to Paul Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.