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:
1st Sheet:-
2nd Sheet:-
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.
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
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.
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 🙂
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!!
Very Useful. Many Thanks.
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
.Save
.Close
End With
'Close the hidden instance of Microsoft Word.
wdApp.Quit
'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?
Dear team what if I have to past the data in existing file how can I open the existing file to past the data
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?
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 SubHappy Learning,
Team Excel Tip
Hello Team Excel Tip , Thank you so much for your help !!! It works .
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.
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.
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.
"I use this information.
I dude, how to format in word the information
to copy excel, I print and cut information.
Thanks,
Regards. "