Word to Excel

[calvinle]

Hi,

I found a code for word to excel, however it will save all the worksheet to a word, but I want to be able to only set in the vba the sheet name like Sheet1, how should I mod the code below?

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

Thanks.

[Tinbendr]

Replace this

For Each ws In ActiveWorkbook.Worksheets

With

Set WS = Worksheets("Sheet1")

And remove (or comment out) the

Next WS

[calvinle]

Thank you. Was missing out that Next WS.

Thanks.

[calvinle]

Now, my code become like this, after I mod it, but why does it create 2 word doc? 1 called Template with nothing, and the other called Document with the data?
Also, how should I write for to have paste with No Spacing?

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
    Set ws = Worksheets("English Letter")
        Application.StatusBar = "Copying data from " & ws.Name & "..."
        ws.UsedRange.Copy ' or edit to the range you want to copy
        wdDoc.Range.Paste
        Application.CutCopyMode = False
    Set ws = Nothing
    Application.StatusBar = "Cleaning up..."

    Set wdDoc = Nothing
    wdApp.Visible = True
    Set wdApp = Nothing
    Application.StatusBar = False
End Sub

[Tinbendr]

Hmm, don't know. It doesn't do it on my Word 2010.

Do you have Word start with a particular template?

[calvinle]

Right, there was a template for every doc. It's fine now. But how should I paste with No Spacing?
Thanks.