Results 1 to 4 of 4

Copy excel sheets to Word Tables

Threaded View

oneillp1 Copy excel sheets to Word... 02-25-2013, 09:59 AM
OllieB Re: Copy excel sheets to Word... 02-25-2013, 10:05 AM
oneillp1 Re: Copy excel sheets to Word... 02-25-2013, 11:16 AM
OllieB Re: Copy excel sheets to Word... 02-25-2013, 12:00 PM
  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.

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