+ Reply to Thread
Results 1 to 4 of 4

Copy excel sheets to Word Tables

Hybrid View

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Copy excel sheets to Word Tables

    Any help would be appreciated, I found a macro that works semi-correctly, I need to copy all sheets from a workbook and paste them into ONE word document. The macro that I am using is:


    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

    This macro works but it makes all the sheets pasted into separate documents and I need them all in the same document.

    Thanks for any input.
    Last edited by oneillp1; 02-25-2013 at 11:14 AM.

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Copy excel sheets to Word Tables

    Two things,

    One, the forum rules stipulate that you must enclose your code with CODE tags. Please edit your post, select the code and click on the # button.

    Secondly, I have looked through the code and all worksheets are copied into the same document as paragraphs. Your statement that all worksheets are copied into separate documents is not something that is supported by the code posted.
    If you like my contribution click the star icon!

  3. #3
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Copy excel sheets to Word Tables

    Sorry about the CODE tags, this is the first time I am posting to this forum. I realize that the code states that the all sheets are populated into one document, however when I run this code on a Workbook with four sheets, it returns 4 documents.

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Copy excel sheets to Word Tables

    Sorry, but when I run the code posted it creates one single document with multiple paragraphs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1