Excel VBA, Replace value, Copy, revert to original then paste

Question

I'm trying to make a subroutine that gets the value from a cell or range, replaces the occurrence of "{NAME}" in the cell's value with let's say "John", select and copy the modified range to be pasted somewhere and reverts the original cell value back to "{NAME}".

I got some messy code going, the logic works but whenever I paste the clipboards content I get the "{NAME}" value and not "John".

Sub BrandTemplate()
    If Worksheets("Sheet1").OLEObjects("Option_1").Object.Value = True Then
        Dim tmp As String
        Dim n As String
        Dim ss as String
        tmp = "John"
        ss = Worksheets("Sheet1").Range("A1").Value
        n = Replace(ss, "{NAME}", tmp)
        MsgBox n
        Worksheets("Sheet1").Range("A1").Value = n
        Worksheets("Sheet1").Range("A1").Copy
        ss = Worksheets("Sheet1").Range("A1").Value
        MsgBox ss + " ss content after replace"
        n = Replace(ss, tmp, "{NAME}")
        MsgBox n + " n content after restore"
        Worksheets("Sheet1").Range("A1").Value = n
        MsgBox n + " n cell content"
    End If
End Sub

I believe Excel gets the content of the cell selected to be copied in real time as you paste.. not just the static content of the cell when you instructed to copy.

Is there a way to accomplish this?

I assume an alternate way would be to include the forms 2.0 object and copy directly the strings to the clipboard?

Can a guru enlighten me?

Thanks for your time!....

Do I understand this correctly: You want to place the contents of a given cell in the clipboard, with a certain part of it replaced? This would take Excel out of the equation. If so, have a look at https://stackoverflow.com/questions/5552299/how-to-copy-to-clipboard-using-access-vba. — Leviathan, May 13 '17 at 14:44
Feel free to post this with an example as an answer and accept it yourself, this way, others stumbling accross this question will benefit too. — Leviathan, May 13 '17 at 14:56
Why not replace the token in the destination cell (ie. *after* the paste) ? — Tim Williams, May 13 '17 at 22:30

score 0 · Answer 1 · answered May 13 '17 at 15:11

Here is a super simple generic example:

Sub TyS()
    Dim r1 As Range, r2 As Range

    Set r1 = Range("A1:A10")
    Set r2 = Range("B1:B10")

    r1.Replace What:="{NAME}", Replacement:="John"
    r1.Copy r2
    r1.Replace Replacement:="{NAME}", What:="John"


End Sub

Excel VBA, Replace value, Copy, revert to original then paste

1 Answers1