Copy 3 named ranges into 1 and paste value

[msquared99]

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

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

 

[Luke M]

Could do this with just the formula, but if you need a macro

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

 

[msquared99]

Thanks Luke that works perfect!