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

 

img1

 

  • 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

 

img2

 

  • 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 & "..."
    ws.UsedRange.Copy
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
    Application.CutCopyMode = False
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
  If Not ws.Name = Worksheets(Worksheets.Count).Name Then
        With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
            .InsertParagraphBefore
            .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
    Else
        .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 info@exceltip.com

Users are saying about us...

  1. “I use this information.
    I dude, how to format in word the information
    to copy excel, I print and cut information.
    Thanks,
    Regards. “

  2. 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.

  3. 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.

  4. 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.

  5. 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
              Next
          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

  6. 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?

  7. 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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube