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

[dwachtveitl]

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

[ajryan88]

Hi,

Try the solution in the attachment...

I hope this helps

[Vikas_Gautam]

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

If you wanna fix the range...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

It takes one argument ie x as range...

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


Don't forget to click *

[ajryan88]

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

[dwachtveitl]

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

[ajryan88]

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

[dwachtveitl]

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

[ajryan88]

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

[Vikas_Gautam]

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

Please Login or Register  to view this content.

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

Don't forget to click *

[dwachtveitl]

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

[ajryan88]

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