• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy 3 named ranges into 1 and paste value

OK, I know this is simple, but I have 3 named ranges in worksheet1 and want to combine data from 3 named ranges into one and paste value that into worksheet2.

Range 1 = COID
Range 2 = CoSet
Range 3 = Service

New Range = COID&CoSet&Service

I would end up with something like this in columnA of worksheet in the range A2:A100 - 12345ATeamMowLawn

Code:
Sub Copy_Combine()
Dim RR As Range

With Worksheets("Sheet1")
Set RR = Application.Union([COID], [CoSet], [Service])
End With

'Copy and paste value RR into worksheet2
Worksheets("Sheet2").Range("A2").Select = RR

End Sub
 
Could do this with just the formula, but if you need a macro
Code:
Sub ConcatValues()
Application.ScreenUpdating = False
With Worksheets("Sheet2").Range("A2:A100")
    .Formula = "=INDEX(COID,ROW(A1))&INDEX(CoSet,ROW(A1))&INDEX(Service,ROW(A1))"
    .Copy
    .PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Back
Top