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:
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
To test the code, let us insert 2 sheets & add random numbers. Following is the snapshot of both the worksheets:
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.
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 firstname.lastname@example.org
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.