Copy and Paste a range into Word

[Michael D. Ward]

Hi All.....

I am trying to copy the same range from a number of worksheets into a
Microsoft Word document. Specifically:

1. Open an existing Word template document which has the top 50% occupied
by text.
2. Move the cursor to the end of the document
3. Paste the clipboard as an Excel Worksheet Object (only works through
'Paste Special')
4. Save the document
5. Open a new template document

I would like to repeat this for as many worksheets as each instance calls
for (max of 10)

I have tried the following:

Private Sub Transfer_Data()
Dim Biosheets As Integer, i As Integer, PageNum As Integer

-------------------------------------------------------------
Biosheets = Worksheets.Count - 1
For i = 1 To Biosheets

With Worksheets("BioProcessor " & i)
Range("E2:AC10").Copy
End With

Application.ActivateMicrosoftApp xlMicrosoftWord

Documents.Open Filename:="""BioprocessorExperiment Form.doc"""
With Selection

.MoveEnd
.Paste
.SaveAs Filename:="BioprocessorExperiment Form " & Biosheets &
".doc"

End With

Next i

End Sub
----------------------------------------------------------------

Any help would be greatly appreciated!!
Thank you

Michael

[Tim Barlow]

Michael,

Try something like (you need to add the reference to
Microsoft Word in Tools>References):

Static WordObj As Word.Application

Worksheets("Sheet1").Range("E2:AC10").Copy

Set WordObj = Nothing
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("C:\My Documents\Test.doc")

'If you want to see what it's doing
WordObj.Visible = True

WordObj.Selection.EndKey Unit:=wdStory
WordObj.Selection.Paste

WordObj.ActiveDocument.SaveAs
FileName:="BioprocessorExperiment Form " & Biosheets
& ".doc"
WordObj.Quit

Set WordObj = Nothing

Once you've got it working correctly for one sheet, you
can add your loop around it.

HTH

Tim

>.
>

[Michael D. Ward]

Tim,

Thank you VERY MUCH! That did the trick!

Michael

"Tim Barlow" <tba...@trl.co.uk> wrote in message
news:175a701c1377c$745849a0$a5e62ecf@tkmsftngxa07...