trying to use PasteAndFormat method in Excel VBA is not working

A

AdamLeeeee

I am having trouble pasting an Excel chart into Word. I am trying to use the
PasteAndFormat function, but no matter how I try to format it, I keep getting
errors. Does anyone know the correct way? I have added the Word control
libratry into my Excel VBA project. See the code below. I am using Office
2003 Professional Edition:

Public Sub CommandButton2_Click()
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlBook = Excel.ActiveWorkbook
Set xlSheet = xlBook.ActiveSheet

Dim appWord As Word.Application
Dim xlWordDoc As Word.Document
Dim wdRange As Range

Set appWord = CreateObject("Word.Application")

appWord.Application.Visible = True
Set xlWordDoc = appWord.Documents.Add
Dim CopyArea As Variant
Dim PrintRange As Variant
Dim PasteRange As Variant
Dim n As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim a As Variant
Dim b As Variant
n = 1
For i = (4) To 217
If xlSheet.Cells(i, 4).Value = Cells(4, 4).Value Then
k = i
j = k + 2
a = xlSheet.Cells(j, 4).Value
b = xlSheet.Cells(4, 4).Value
Do While (a <> b) And (j < 219)
j = j + 1
a = xlSheet.Cells(j, 4).Value
b = xlSheet.Cells(4, 4).Value
Loop
Set PrintRange = Range(Cells(k - 1, 4), Cells(j - 2, 11))
PrintRange.Select
Selection.Copy
Set PasteRange = xlWordDoc.Paragraphs(n).Range
xlWordDoc.Paragraphs(1).Range.PasteAndFormat Type:=wdChartPicture 'This is
the line that doesn't work
xlWordDoc.Paragraphs(1).Range.InsertBreak Type:=wdPageBreak
i = j - 1
n = n + 1
End If
Next i
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top