Forum Discussion
Applying a macro to multiple cells.
I need help with a macro issue.
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 be the same for 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- without overwriting 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.
Hope this makes sense. Thank you in advance.
- Heidi
1 Reply
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
