VBA - SendKeys Paste Syntax Error - While replacing excel cells

Question

I'm trying to use SendKeys to copy a word from one cell into the replace (CTRL+F) function.

The copy bit is fine but this spreadsheet is going to be used as a template so the variable is what is in that cell meaning i'm using send keys.

I'm open to other ideas.

Code is below.

  Range("E5:G5").Select
Selection.Copy
Sheets("New Project Schedule").Select
Selection.Replace What:="New Merchant", Replacement:= SendKeys "^v" , LookAt= _
    :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Sheets("Out of Scope").Select
Selection.Replace What:="New Merchant", Replacement:= SendKeys "^v", LookAt= _
    :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Sheets("New Project Schedule").Select
Selection.Replace What:="Merchant", Replacement:= SendKeys "^v", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Sheets("Out of Scope").Select
Selection.Replace What:="Merchant", Replacement:= SendKeys "^v", LookAt:=
    xlPart , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Answer 1

You don't need to copy and paste - something like this should do:

Dim avSheets, vSheet
Dim sWord                 As String

avSheets = Array("New Project Schedule", "Out of Scope")

sWord = Range("E5:G5").Value

For Each vSheet In avSheets
    With Sheets(vSheet).Cells
        .Replace What:="New Merchant", Replacement:=sWord, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                 SearchFormat:=False, ReplaceFormat:=False
        .Replace What:="Merchant", Replacement:=sWord, LookAt:=xlPart, _
                 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
Next vSheet