Solved: Merge two codes

[Lartk]

Is there a way to merge the below two codes? They are being written in excel so after I copy and paste excel data into word, I would like to save the word document but I dont want to have to manually click the word document to make it the activeworksheet. So I'm thinking if there is a way to merge these two codes, that would work best. Is there a way?

[vba]Sub PasteToWord()

Dim AppWord As Word.Application

Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
' Change the range to suit your needs. See the How to Use for this code
Sheets("Sheet1").Range("A3:H28").Copy
AppWord.Documents.Add
AppWord.Selection.Paste

Application.CutCopyMode = False

Set AppWord = Nothing

ActiveWindow.SmallScroll Up:=75
End Sub

Sub Test()
ActiveDocument.SaveAs2 "C:\Users\klartigue\Desktop\Test.pdf", 17
End Sub[/vba]

[GTO]

Howdy there,

Does this help?
[vba]Sub Paste2Word()
Dim WD As Word.Application
Dim wdDoc As Word.Document

' If early-bound
Set WD = New Word.Application

With WD
.Visible = True
' Set a reference to the document
Set wdDoc = .Documents.Add
End With

ThisWorkbook.Worksheets("Sheet1").Range("A3:H28").Range("A3:H28").Copy

WD.Selection.Paste
Application.CutCopyMode = False
' I simply chose the same folder that the wb is in
wdDoc.SaveAs2 ThisWorkbook.Path & "\Test", 17
' Optionally, ditch the created document and the created app instance
wdDoc.Saved = True
WD.Quit
Set wdDoc = Nothing
Set WD = Nothing
End Sub[/vba]

Mark

[snb]

[vba]Sub M_snb()
Sheets("Sheet1").Range("A3:H28").Copy
With CreateObject("Word.Document")
.application.Selection.Paste
.SaveAs2 "C:\Users\klartigue\Desktop\Test.pdf", 17
.close 0
end with
End Sub[/vba]

[Lartk]

The above works great, and I changed it to save in word instead of pdf. Two more things, right now when the excel data is copied and pastes into word, it takes up two pages in word (although the second page is blank)so is there a way to make it fit to one page in word?

Also, i have a "Master.doc" word document that has a nice heading format. Is there a way to instead of opening a new word document, open the Master.doc and paste the excel data into there and then I can "Save as"?

If i could incorporate a couple codes to the below code that would make the word document fit to one page and open the Master.doc and paste the excel data into there (instead of opening a blank word doc each time) that would be great. Can you help?

[vba]Sub M_snb()
Sheets("Sheet1").Range("A3:H28").Copy
With CreateObject("Word.Document")
.Application.Selection.Paste
.SaveAs2 "C:\Users\klartigue\Desktop\Test.doc"
.Close 0
End With
End Sub
[/vba]

[snb]

[VBA]

Sub M_snb()
Sheets("Sheet1").Range("A3:H28").Copy
With CreateObject("G:\OF\master.doc")
.Application.Selection.Paste
.SaveAs2 "C:\Users\klartigue\Desktop\Test.doc"
.Close 0
End With
End Sub

[/VBA]

[Lartk]

That works great, thank you. I also copy and pasted the data as a picture which helped to.

Also, there is a name in the excel sheet in cell A6. Can I reference that cell and have the word document save as the name in cell A6?

[snb]

Yes, you can. Just analyse the code and you will find out yourself.

[Lartk]

I tried this but its not working

[vba]Sub Test()
Dim FName As String
Dim FPath As String
Sheets("Sheeet1").Range("A3:G80").CopyPicture Appearance:=xlScreen, Format:=xlPicture
With CreateObject("Word.Document")

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

FPath = "C:"
FName = Sheets("Sheet1").Range("A6").Text

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

End With
End Sub[/vba]

[Lartk]

Nevermind, i got it. I added the path and it works

[Lartk]

Is there a code to have my cursor go three "returns" down the screen?

[Aussiebear]

Is there a code to have my cursor go three "returns" down the screen?

Please start a new thread