Solved: Error on code to copy from excel and paste to word

[Lartk]

Sub Test()
Dim FName As String
Dim FPath As String

Sheets("Sheet1").Range("A3:G30").CopyPicture Appearance:=xlScreen, Format:=xlPicture

With GetObject("C:\Users\kl\Desktop\Master Format.doc")

.Application.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False

FPath = "C:\Users\kl\Desktop"
FName = Sheets("Sheet1").Range("A6").Text

.SaveAs2 Filename:=FPath & "\" & FName
.Close 0

End With
End Sub



I am getting an error when I try to run the above code. I get Run-time error '91': saying Object variable or With block variable not set.

Can you see any reason why I am getting this error?

[Teeroy]

The Application in that code is Excel, not Word, and if you don't have a reference set to the Microsoft Word XX.0 Object Library then you won't get the wdPasteEnhancedMetafile constant.
The following might help give you a starting point but I freely admit to being rubbish with Word VBA.

Sub Test2()
Dim FName As String
Dim FPath As String
Dim wordApp As Object
Dim doc As Object

Sheets("Sheet1").Range("A3:G30").CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set wordApp = CreateObject("Word.application")
Set doc = wordApp.documents.Open("C:\Users\kl\Desktop\Master Format.doc")
doc.Characters.Last.Select
With wordApp
.Selection.PasteSpecial Link:=False, DataType:=9, _
Placement:=wdInLine, DisplayAsIcon:=False
End With
FPath = "C:\Users\kl\Desktop"
FName = Sheets("Sheet1").Range("A6").Text

With doc
.SaveAs2 Filename:=FPath & "\" & FName
.Close 0
End With
wordApp.Quit
Set wordApp = Nothing
Set doc = Nothing
End Sub

[Lartk]

That works great!!

I tried to alter the code slightly to tell it to copy and paste the range from A5 to the lastrow(indicated by column G). I am working with this, can you see whats wrong here with the reference to copy the area from A5 -G5, down to the lastrow

Sub Test2()
Dim FName As String
Dim FPath As String
Dim wordApp As Object
Dim doc As Object
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
For i = 5 To Lastrow


Sheets("Sheet1").Range(.Cells(i, "A"), .Cells(i, "G")).Select.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set wordApp = CreateObject("Word.application")
Set doc = wordApp.Documents.Open("C:\Users\kl\Desktop\Master Format.doc")
doc.Characters.Last.Select
With wordApp
.Selection.PasteSpecial Link:=False, DataType:=9, _
Placement:=wdInLine, DisplayAsIcon:=False
End With
FPath = "C:\Users\kl\Desktop"
FName = Sheets("Sheet1").Range("A6").Text

With doc
.SaveAs2 Filename:=FPath & "\" & FName
.Close 0
End With
wordApp.Quit
Set wordApp = Nothing
Set doc = Nothing
End Sub

[Lartk]

I figured it out!

Range("A5:G" & Cells(Rows.Count, "G").End(xlUp).Row).Select

[Lartk]

Right now, I am counting the last row using the below formula:

Range(("A5:G5") & Cells(Rows.Count, "A").End(xlUp).Row).Select

Is there way to count row A + 1?

[Lartk]

Range(("A5:G") & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select

Got it!

[Teeroy]

In the last full code you posted you are creating objects and setting variables repeatedly inside a loop; not a good idea. There does not appear to be closure of the new FOR or WITH blocks. You should possibly restructure your code.

Also a general way to get a cell referenced from an end range is to use offset. e.g.

Range("A5:G" & Cells(Rows.Count, "G").End(xlUp).offset(1,0).Row)