Coding from Excel to Word

[cheoke]

I have the code to write from an excel sheet to a word document, but I have a couple of buttons in which I want to write in the same word document.

Dim wordApp As Object
    Dim fNameAndPath As String
    fNameAndPath = "C:\Documents and Settings\Gerard\My Documents\BOM.doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Documents.Open (fNameAndPath)
    wordApp.Visible = True
    Worksheets("Sheet3").Range("A1:S16").Copy
    If Sheets("Sheet3").Activate Then
    wordApp.Selection.PasteSpecial
    End If

Thats the code for command button 1, but i have a second command button in which I want a certain range to paste in the same BOM word document. Can anyone help? Thank You

 

[Bill_Biggs]

If I am following you correctly, why not have a message box pop up after the initial code is run, asking if you want to write in the additional range on the same sheet. If yes, then do it, if no, exit sub. Then you won't need the additional button.

 

[cheoke]

How would I modify my existing code to add this?

 

[Bill_Biggs]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> macro1()<br><SPAN style="color:#00007F">Dim</SPAN> wordApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> fNameAndPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    fNameAndPath = "C:\My Documents\BOM.doc"<br>    <SPAN style="color:#00007F">Set</SPAN> wordApp = CreateObject("Word.Application")<br>    wordApp.Documents.Open (fNameAndPath)<br>    wordApp.Visible = <SPAN style="color:#00007F">True</SPAN><br>yesno = MsgBox("Do you have more than one range you wish to place on the BOM Sheet?", vbYesNo)<br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> yesno<br><SPAN style="color:#00007F">Case</SPAN> vbNo<br>    Worksheets("Sheet3").Range("A1:S16").Copy<br><SPAN style="color:#00007F">Case</SPAN> vbYes<br>    Worksheets("Sheet3").Range("A1:S16" and Additional Range Here).Copy<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet3").Activate <SPAN style="color:#00007F">Then</SPAN><br>    wordApp.Selection.PasteSpecial<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Something along these lines. This is untested.