Print Multiple Sheets as Word Docs

[LLEW1]

Hi all,

Im looking for code to deal with multiplesheets in the same workbook that I want to print as a single word document.. any thoughts on the VBA code for this?

I have code that handles it one sheet at a time for PDF print, but need these two alterations so it prints all sheets, and in word.

Any thoughts appreciated....Thanks

[lucas]

Here's one to try. Each sheet on a page:
[VBA]Option Explicit
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
[/VBA]

[LLEW1]

Hi Lucas,
sorry for the slow reply, caught up here in testing yesterday.

This seems to work a treat so far, just putting it within the app Im building and will confirm.

Thanks heaps for this so far...

[LLEW1]

Hi Lucas,

just catching up - this has worked well; and prints all sheets in the workbook. What is the best way to print just specific sheets in the workbook?

[lucas]

Are you going to select different ones each time and need to choose from a list or will they be the same each time and can be hard coded?

[LLEW1]

Hi, thanks for following up...

It could be different each time, so a selection option would be great.

One other - I note the pagintion code above, will this need change? I may specify a different range per sheet, not juse UsedRange?

[GTO]

Greetings,

Just as to selection different sheets, maybe?
[vba]
'For Each ws In ActiveWorkbook.Worksheets
For Each ws In ActiveWindow.SelectedSheets
[/vba]

Hope this helps,

Mark

[LLEW1]

Hey GTO,

thanks will try soon. You and Lucas (Steve) have been really helpful here.
Will let you know where this gets me.. thanks again.