Select range of cells on sheet 2 and concatenate into one cell on sheet one

Hello:

I am looking for VBA code that will take a range of cells B2:E2, from sheet 2, concatenate them with a space between each cells text string, and paste the result into cell A2 on sheet 1.

I hope I have explained this well enough, but please feel free to ask for clarification.

I greatly appreciate the assistance.

David

Hi,

Try the solution in the attachment...

I hope this helps

If I understood it correctly... the following code will do....

If you wanna fix the range...

sub Concaty()
s=0
For each cell in worksheets("Sheet2").range("B2:E2")
Concaty=concaty & Space(s) & cell.value
s=1
next
worksheets("Sheet1").range("A2").value=Concaty
End sub

if you wanna specify the range yourself then use following code...

Function Concaty(x as range)
s=0
For each cell in x
Concaty=concaty & Space(s) & cell.value
s=1
next
End Function

It takes one argument ie x as range...

Press Alt + F11 then insert module.... copy the function...
and enjoy..


Don't forget to click *

Hello:

Thank you but I am receiving an error message "expected function or variable" and it highlights the second concaty in line as follows:

Concaty = Concaty & Space(s) & cell.Value

Thank you, David

@Vikas: please use code tags when posting code. Also, please use indenting in your code to make it easier to read

Thanks, I am getting an error message saying invalid use of Me. Any idea why?

The use of Me is in the worksheet context. If you are using my code in a custom module (rather than a worksheet module), then change "Me" to Worksheets("WorksheetName"), or just simply ActiveSheet

Add Dim concaty As String on the line following Sub Concaty()

I am sorry dwach.. use the following code..
it will definitely work...

sub Concaty()
Dim x as string
s=0
For each cell in sheets("Sheet2").range("B2:E2")
x = x & Space(s) & cell.value
s=1
next
worksheets("Sheet1").range("A2").value=x
End sub

if it gives any error then check the sheet names..

Don't forget to click *

That did it Vikas. Thank you very much, I should have asked 12 hours ago.

Don't forget to mark this thread as solved, and you can help those who have helped you by clicking on the * next to their posts to say thanks and add to their reputation