BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Copy Excel Worksheets to Microsoft Word



Code:

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

Anonymous said...

Thanks a LOT!!!!!

brendan chou said...

Wow! How about the old copy and paste as a Microsoft Excel Worksheet Object?

Jose SISA / Alfred WALLACE said...

hello ! thanks,but can you show, please, how to write a filigrane in the word page with the content of a cell ?
thx again !