So, I currently have a large Excel workbook with over 1000+ cells with content in them.
My issue: I have a column, column A I will call it, that has different text in each cell. For instance, using first names, A1 is Mark, A2 is Sam, A3 is Grace, and so on. Now, in each cell I need to add/paste text, say a last name. This text, which is in my clipboard, will bethe samefor every cell in column A. So I need to paste "Appleseed" into A1, A2, A3, and so on resulting in Mark Appleseed, Sam Appleseed, and Grace Appleseed-withoutoverwriting the first names.
What I need:
A macro that takes the text from my clipboard and pastes it into the cells.
The above macro to apply itself to all necessary cells in one or only a few clicks- column A has 1517 "first names" so can not do this manually.
I am not wildly enthusiastic about the use of the clipboard; more often than not is contains the leftovers from some past operation. It also requires references be set to the "Microsoft Forms 2.0 Object Library".
That said, the following code appears to do what you require
Sub extendName() Dim DataObj As New MSForms.DataObject Dim rng As Range Dim v() Dim i As Long Dim S As String DataObj.GetFromClipboard S = DataObj.GetText Set rng = Range("firstName") v = rng.Value For i = 1 To UBound(v) v(i, 1) = v(i, 1) & " " & S Next rng.Value = v End Sub