Can you ppl help me test this macro?

[thedark123]

I seems to get error while using this macro I get somewhere in the internet

Can fix the error for me?

Copy worksheet information to Word

The macro below copies all worksheets in a workbook to a new Word document. Each worksheet starts on a new page in the Word document:



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

[Norie]

What error are you getting?

[thedark123]

Error Message:



Compile Error:
User-defined type not defined


at this part:

ws As Worksheet

[lucas]

From the visual basic editor go to tools references and set a reference to the MS word object library. Note the first 2 lines of the code:

' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library

[lucas]

This macro runs from excel

[lucas]

attached is a working copy(it works in office 2003) if you have a different version then set it for the version that works with your office software.

For instance in 2003 it is "Microsoft Word 11 Object Library"
If you have an earlier or later version you will need to set it to that version, might be 9 or 10 or 12

[lucas]

Hit alt+F11 to open the visual basic editor. Then go to tools - References and look for this window. Scroll down until you find the word library

[Norie]

Steve

The code could use late binding to avoid having to use references.

Option Explicit
Const wdCollapseEnd = 0
Const wdPageBreak = 7
Const wdNormalView = 1

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 Object, wdDoc As Object, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = CreateObject("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

[lucas]

Valid point Norie. Saves a lot of headaches for some. I think this is pretty well worked out after you added that point. Don't see why the op shoudn't be able to use the macro. Maybe we'll hear from them soon.

It is a useful script