VBA currently exports each tab into a separate word document. Need to update it to export each tab onto separate page in same Word doc!

[aw12g10]

Hi all,


Pretty much as the title states! I'm using a Macro I've had for a while to export certain data ranges into individual word documents. I need to develop this into the macro exporting each tab into a new page on the word document. The VBA I'm using is:

Option Explicit
 
Sub PasteToWord()
     
    Dim AppWord         As Word.Application
     
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
     '  Change the range to suit your needs. See the How to Use for this code
    Sheets("UK Digital").Range("B1:Bk35").Copy
    AppWord.Documents.Add
    AppWord.Selection.Paste
     
    Application.CutCopyMode = False
     
    Set AppWord = Nothing
     
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
     '  Change the range to suit your needs. See the How to Use for this code
    Sheets("Print").Range("B1:BK37").Copy
    AppWord.Documents.Add
    AppWord.Selection.Paste
     
    Application.CutCopyMode = False
     
    Set AppWord = Nothing
    
        Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
     '  Change the range to suit your needs. See the How to Use for this code
    Sheets("International").Range("B1:BN24").Copy
    AppWord.Documents.Add
    AppWord.Selection.Paste
     
    Application.CutCopyMode = False
     
    Set AppWord = Nothing
End Sub

As you can see, I'm creating a new document for each tab; "UK Digital, Print, International" etc. Please can you help me with getting these into the same document on individual pages?


Thanks


Alex

 

[Macropod]

Try:

Sub PasteToWord()
    Dim AppWord As New Word.Application
    With AppWord
      .Documents.Add
      With .ActiveDocument.Range
        Sheets("UK Digital").Range("B1:BK35").Copy
        .Characters.Last.Paste
        .InsertAfter Chr(12)
        Sheets("Print").Range("B1:BK37").Copy
        .Characters.Last.Paste
        .InsertAfter Chr(12)
        Sheets("International").Range("B1:BN24").Copy
        .Characters.Last.Paste
      End With
    AppWord.Visible = True
    End With
    Set AppWord = Nothing
    Application.CutCopyMode = False
End Sub