Solved: Paste multiple items from clipboard

[Thrillho]

OS: Windows 7
Office: 2010

I have a standard template (template.docx) for a report which gets filled out about a few hundred times a year which I'm trying to automate. One of the elements of these reports is who the report is done by. Generally one person fills out the report, but up to four people can have their names on it.

My goal is to create a macro to fill out each person's Name, Title, and Email based on another document (signatures.docx). This document will contain about 50 names / titles / emails.

In the code below, I've been able to crudely get the first name filled in successfully. Upon running, a window pops up asking who you're looking for (initials of the individual), then it opens signatures.docx, searches in the table for those initials, moves to the next cell to where the signature is, copies the signature, closes signatures.docx, then pastes the signature in the appropriate spot in template.docx.

My problem is that while I can copy multiple names (not shown in code), I cannot paste each one. I don't know how to say something like "as = Person A signature" in VBA, and have "as" pasted somewhere in Word (and bs, cs, ds).

A very crude solution to this would be to have it repeat itself four times. But I would rather not have the document open and close four times.

Sub signature()
a = InputBox("Name")
ChangeFileOpenDirectory "C:\Users\thrillho\Desktop\Report\"
Documents.Open FileName:="Signatures.docx", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = a
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.Copy
ActiveWindow.Close
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Submitted By"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.PasteAndFormat wdFormatOriginalFormatting
Selection.Delete Unit:=wdCharacter, Count:=7
End Sub

Thoughts on how I should go about solving this?

Thrillho

[fumei]

Use a userform, rather than an InputBox.

The userform can build a list of names via a textbox and a "Another Name" button; or a combobox with a big list that has selections.

When the person is done - clicking a "Done" button - open your Signatures document, and grab each signature.

It would be up to you to define more clearly what "appropriate spot" actually means.

[Thrillho]

Thanks for the quick reply.

Chances are I'll end up using a userform for this full automation (many more items in it than just find copy & paste). But at the moment, using a userform doesn't solve my problem - I still don't know how to paste from clipboard.

This is what I'd like it to do:

From template.docx, run macro, opens up userform with A B C D values.
A = Fred
B = Bob
C = Henry
D = Julia

Open signatures.docx
Find A, copy As
Find B, copy Bs
Find C, copy Cs
Find D, copy Ds

Close signatures.docx, defaulting back to template.docx
Go to signature-spot-A, paste As
Spot B, paste Bs
Spot C, paste Cs
Spot D, paste Ds

End

I can have this whole thing run if I only have one variable (A). To get B C D, I would need to rerun it for each one, which means reopening the signatures.docx file - something that isn't overly efficient. I don't know the proper formatting to have As Bs Cs Ds stored either in the clipboard or in VBA.

After typing this out, I think I might understand what you're getting at. Start a userform, have the user input the start data, it searches signatures.docx and pastes in userform, closes signatures.docx, pastes values from userform to template.docx? Now all I need to know is how to get the value from signatures.docx in to the userform. (Much easier for me in excel, I could just say to take the value in cell A3).

And "appriopriate spot" just means the location I want to paste it in template.docx. On the title page in the bottom right corner there is a spot for 4 names / titles / emails - these values need to be pasted there. Navigating that area I can do.

[Thrillho]

Figured out how to store within the code.

Change

Copy.Selection
to

variable ["As" in my case] = Selection


To use this value,

Change
Selection.PasteAndFormatto
Selection = variable



I should be able to make this work now.