Pasting from excel to word on specific line or table

klutch

New Member
Joined
Jun 7, 2018
Messages
38
Hello, I am working on a project to copy/paste a single cell from various unique excel sheets to their correlating files.
This is what I have so far:
Code:
 Sub CopyandPaste()
Dim myfile
Range("j2").Select
Selection.Copy
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim wdapp As Word.Application
Set wdapp = CreateObject("Word.Application")
wdapp.Visible = True
Dim wddoc As Word.document
Set wddoc = wdapp.Documents.Open(myfile)
End Sub
What I am looking for is code to not only paste it into word, but into the last table of the document.
So far I have come up with using this:
Code:
Sub PasteToWord()
     Dim rangetocopy As Range
     Set rangetocopy = Range("j2").CurrentRegion
     Dim wordapp As Word.Application
     wordapp.Visible = True
     Dim worddoc As Word.document
     Set worddoc = wordapp.Documents.Add
     rangetocopy.Copy
     worddoc.Words(1).PasteExcelTable False, False, False
     
     
     
     
End Sub
or this:
Code:
selection.GoTo What:=wdGoToLine, Which:=wdGoToFirst, Count:= 39, Name:="" Selection.Find.ClearFormattingWithSelection.Find

.text=""
.Replacement.Text="" 
.Forward=True
.Wrap=wdfindcontinue
.Format=false
.MatchCase=False
.MatchWholeWord=False 
.MatchWildCards=False
.MatchSoundslike=False
.MatchAllWordForms=False
End With
I have had trouble implementing either of these to run effectively. Any solutions?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Is there anything in the Excel workbook to tell Word what files to open? As for the Word table, which cell is to be updated, and is there any content there that needs to be preserved?
 
Upvote 0
The code I currently have allows me to select which file to open, and it copies the cell that I want. I am looking for code to paste the copied data into the word doc at the end. I don't know if I would be best off just running the excel macro to paste it in word, and then set up a separate word macro to put it where I want.
 
Upvote 0
You could do that with just:
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Range("J2").Copy
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
End Sub
 
Upvote 0
You could do that with just:
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Range("J2").Copy
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
End Sub

That is so close! Is there a way for me to match the paste to the format of the word doc?
 
Upvote 0
Okay so I assigned a bookmark to the table, naming it "PRTable"
This is how I coded it to appear in Cell (2,4).
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
Dim oTable As Table
Dim WriteToDoc As Document
Dim Sourcedoc As Document
Set WriteToDoc = ActiveDocument
myfile = Application.GetOpenFilename(, , "Browse for Document")
Range("e4").Copy
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
Set oTable = Sourcedoc.bookmarks("PRTable").Range.Tables(1)
WriteToDoc.Range.InsertAfter oTable.Cell(2, 4).Range.Text & vbCrLf
Sourcedoc.Close
Set oTable = Nothing
Set Sourcedoc = Nothing
 
End Sub
I am getting a run-time error '94': object variable or With Block variable not set.

Any solutions?
 
Upvote 0
That is so close! Is there a way for me to match the paste to the format of the word doc?
Depending on what you want to achieve, you could change:
wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
to:
wdDoc.Range.Characters.Last.PasteExcelTable False, True, False
or:
wdDoc.Range.Characters.Last.PasteExcelTable False, True, True
or even:
wdDoc.Range.Characters.Last.Paste
 
Upvote 0
Okay so I assigned a bookmark to the table, naming it "PRTable"
This is how I coded it to appear in Cell (2,4).
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
Dim oTable As Table
Dim WriteToDoc As Document
Dim Sourcedoc As Document
Set WriteToDoc = ActiveDocument
myfile = Application.GetOpenFilename(, , "Browse for Document")
Range("e4").Copy
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
Set oTable = Sourcedoc.bookmarks("PRTable").Range.Tables(1)
WriteToDoc.Range.InsertAfter oTable.Cell(2, 4).Range.Text & vbCrLf
Sourcedoc.Close
Set oTable = Nothing
Set Sourcedoc = Nothing
 
End Sub
I am getting a run-time error '94': object variable or With Block variable not set.

Any solutions?
The error is most likely related to your use of Sourcedoc - which you don't define properly and never assign to anything before trying to use it.

Aside from that, I can't really tell what you're trying to do with the modified code - you copy cell J2, then do nothing with it and instead try to insert something from Sourcedoc into WriteToDoc (which you also don't define properly).
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top