Copy, paste, and concatenate in one step

Using VBA, I would like to copy a range of items from one worksheet, paste them into another worksheet and concatenate a suffix on each item all at one time without using a counter and a loop. See attached spreadsheet for illustration.
Who is Participating?
Saurabh Singh TeotiaCommented:
You can perhaps do this..this way...

Sub copypaste()
    Dim lrow As Long

    lrow = Sheets("Start").Cells(Cells.Rows.Count, "a").End(xlUp).Row

    Sheets("Finish").Range("A2:A" & lrow).Formula = "=Start!A2&""Suffix"""
    Sheets("Finish").Range("A2:A" & lrow).Value = Sheets("Finish").Range("A2:A" & lrow).Value
End Sub

Open in new window

EdLBAuthor Commented:
Worked very well. Thanks for the quick response!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.