Copy worksheet information to Word

The macro below copies all worksheets in a workbook to a new Word document. Each worksheet starts on a new page in the Word document:

Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
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
        If ws.Visible Then ' include only visible worksheets
            Application.StatusBar = "Copying data from " & ws.Name & "..."
            ws.UsedRange.Copy ' or edit to the range you want to 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
            ' insert page break after all worksheets except the last one
            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
        End If
    Next ws
    Set ws = Nothing
    Application.StatusBar = "Cleaning up..."
    ' apply normal view
    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

Document last updated 1999-12-20 12:51:27 Printerfriendly version

User comments:

aaskat from Boston, MA wrote (2005-12-14 00:34:47 CET):

HElp with the formatting
Great job on the MAcro ... the one problem that I have is the formatting .... In Excel, in order to display the data on one page, I have used the landscape view and it works perfectly. When it copies to word, it naturally copies it in the same format, but I would like to be able to copy it into the portait format (I don't care if the font gets smaller because of that) or if it can copy the data onto the word document length wise instead of breadth wise (I have to incorporate this generated documents finally into another word report that I already have) Any way of doing this? please need it urgently .... thanks

Ole P. from Norway wrote (2005-06-07 15:51:06 CET):

Re: I was looking a long time for it
Use the information on this page to retrieve the cell ranges for the pages you want to print.
In the macro example above, you can then replace this: ws.UsedRange.Copy
with something like this: ws.Range(coll(3)).Copy
to copy a specific page to Word (e.g. page 3).

Lya from The Netherlands wrote (2005-06-07 15:00:50 CET):

I was looking a long time for it
but I have problem, I want to figure it out myself,but it didn't work, I want 3 out of 10 pages of a worksheet import to Word and a don't know how to do this
if you have the time, do you want to help me ?
Thank you very much, Lya

Ole P. from Norway wrote (2005-02-03 20:43:17 CET):

Re: Problems
Seems to me that you should take a look at the mail merge feature in Word using your Excel workbook as the datasource.

Luiz from São Paulo, Brazil wrote (2005-02-03 17:52:45 CET):

Problems
thats is my first time that i see macro, and i am still have some problem after seeing your example!
what i need to do here, is that i have a excel sheet with hundreds of lines and i need to do a template for each line. Each line has about 10 columns.
i need to put all those line in a template at the word document, but i can´t do it. i don´t know what happend...
after knowing how to import one line from the excel sheet to the word i can do the rest. because its the same thing.
please, if you have any time, i really need your help!!
thanks

Ole P. from Norway wrote (2004-10-08 09:42:58 CET):

Re: how to copy worksheet printpages into word
I couldn't disappoint you :-)
I have added a new example showing how to determine the page ranges for each printed page in a worksheet.
You can combine the information from the new example and this example to be able to copy each page of the worksheet separately into Word instead of copying the whole worksheet using "ws.UsedRange.Copy".

rob carey from sunnyvale, california wrote (2004-10-07 20:24:55 CET):

how to copy worksheet printpages into word
It seems like the macro is almost there. From what I have read, it copies the whole worksheet and fits it to one word page.
My worksheets span multipages. I have each print-formatted to fit on multiple pages.
So, oh ghuru, how to get your macro to read print formatting and copy same print page to msWord page.
This would be great because I am in deep shit right now. I have a wonderful excel document, full of hyperlinks and with the correct worksheet page formatting (it's so easy in excel).
If I have to copy paste to word etc., I will become impotent. For my wife's sake, for the sake of all our unborn children (who may, incidentally, discover the solution to world peace forever, or perhaps to create dripless ice cream) ..... for god's sake, man or woman, throw me a bone. Gimme code, please.

robert carey aka mt everest

Salil Gokhale from Gainesville, Florida, USA wrote (2004-05-12 22:58:22 CET):

Excellent
This is an excellent macro.
For a long time I was trying to figure out a way to insert tables directly from excel into word. You have just pointed me in the right direction.
Thank you very much!!

Angel May G from Coatzacoalcos, Ver., México wrote (2004-04-13 21:19:21 CET):

congratulations
It´s a very good macro