View Single Post
 
Old 06-01-2015, 09:14 PM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,142
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You are actually posting in the Word forum, however the following should do the trick. You don't need to activate the document if working with ranges. The reason each sheet overwrote the previous is that you pasted to the document range, rather than insert a page and paste to that.

Code:
Option Explicit

Sub toWord()
Dim ws As Worksheet
Dim Wkbk1 As Workbook
Dim wdapp As Object
Dim wddoc As Object
Dim orng As Object
Dim strdocname As String

    Set Wkbk1 = ActiveWorkbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    strdocname = "C:\Name.doc"


    'file name & folder path
    On Error Resume Next
    'error number 429
    Set wdapp = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Err.Clear
        'create new instance of word application
        Set wdapp = CreateObject("Word.Application")
    End If
    wdapp.Visible = True
    'define paths to file
    If Dir(strdocname) = "" Then
        'MsgBox "The file" & strdocname & vbCrLf & "was not found " & vbCrLf & "C:\Path\Name.doc", _
         '       vbExclamation, "The document does not exist "
        'Exit Sub
        Set wddoc = wdapp.Documents.Add
    Else
        Set wddoc = wdapp.Documents.Open(strdocname)
    End If
    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("A1:A2").Copy
        Set orng = wddoc.Range
        orng.collapse 0
        orng.Paste
        orng.End = wddoc.Range.End
        orng.collapse 0
        orng.insertbreak 7
    Next ws

lbl_Exit:
    Set orng = Nothing
    Set wddoc = Nothing
    Set wdapp = Nothing
    Set Wkbk1 = Nothing
    Set ws = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote