Solved: script to copy multiple cell values into one cell

[Skopweb]

Hello
I'm looking for a script that allows me to paste values from multiple cell to one single cell (when done manually we hit the F2 key when a cell is selected) ........
example : if i have the following in my MS word
(please refer attachment)

by doing just a copy and paste to excel sheet it will paste into 6 different cells
but if i copy the same and select a cell in excel and hit the F2 key, all of it will paste in one single cell...
i need a script for the second option @ it should paste the values in one cell without hitting the F2....

Thanks in advance
Regards
Skopweb

[Bob Phillips]

Would it be OK to let it paste into multiple cells, then concatenate it?

[Bob Phillips]

BTW, do you want the copy and paste from Word to Excel to be automated, or will you do that and just let the macrio tidy up?

[Skopweb]

Thank you for responding xld.....
I doubt Concatenate will work for me.

The copy and paste on selected cell effort will be manual . only thing is that the user should not go for F2 key to paste the values in one cell.
also Word will be one of the source to copy from as there will be other sources like htm, notepad etc.,

Regards
Skopweb

[Bob Phillips]

It seems to me that you want a macro that will concatenate all of the selected cells after the copy paste has been done. Won't that do it?

[Skopweb]

well i'm not sure. but can you show me what are you reffering to so i can conclude if it suits my needs

Skopweb

[Bob Phillips]

Something like

[vba]
Public Function ConCat()
Dim tmp As variant
Dim cell As Range

For Each cell In Selection

tmp=tmp & cell.Value
Next cell

Selection.ClearContents
Selection.Cells(1,1).Value = tmp
End Function
[/vba]

[Skopweb]

hello vbax
should i enter this code in module,workbook or sheet

regards
skopweb

[Bob Phillips]

That was standard code module.

[Skopweb]

hello xld
this code is not working. i entered the code in module of excel

Regards
Skopweb

[mdmackillop]

Cange it to a Sub
Select the cells in Excel you wish to join then run the sub
[vba]
Sub ConCat()
Dim tmp As Variant
Dim cell As Range

For Each cell In Selection

tmp = tmp & cell.Value
Next cell

Selection.ClearContents
Selection.Cells(1, 1).Value = tmp
End Sub

[/vba]

[Skopweb]

Hello Mdmackillop
your code seems to be working. But i need to run macros macros to perform the concat
Is there a way it performs automatically as i paste.

Regards
Skopweb

[mdmackillop]

This will work on any worksheet change. I don't know that it can be limited to a Paste event

[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ConCat Target
Application.EnableEvents = True
End Sub

Sub ConCat(Target)
Dim tmp As Variant, cell As Range
For Each cell In Target
tmp = tmp & cell.Value
Next cell
Target.ClearContents
Target.Cells(1, 1).Value = tmp
End Sub
[/vba]

[Skopweb]

hello mdmackillop
this seems to be working.
just one more thing........ is there a way to limit this script to a given column or 2 columns (A or B or A&B) in a sheet

thanks in advance
Regards
Skopweb

[mdmackillop]

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.column > 2 then Exit Sub
Application.EnableEvents = False

[/VBA]

With regard to limiting functionality, you could assign Paste and the Concatenate code to a Keyboard Shortcut and use this instead of Control + V.

[Skopweb]

thank you sir for you prompt reply but i doubt this will work only for a given column.
for eg : if i need this code to work on cells in column D only and other columns it should be normal. will this work

Regards
Skopweb

[mdmackillop]

Consider how you might change this line
[VBA]
If Target.column > 2 Then Exit Sub

[/VBA]

[Skopweb]

Thank you

[VBA]If Not (Target.Column = 4) Then Exit Sub[/VBA]

Regards
Skopweb

[mdmackillop]

... and if you need different macros for different columns, you could use Select Case on the column numbers and call your macros/Exit as appropriate.

[Ismail]

Something like

[vba]
Public Function ConCat()
Dim tmp As variant
Dim cell As Range

For Each cell In Selection

tmp=tmp & cell.Value
Next cell

Selection.ClearContents
Selection.Cells(1,1).Value = tmp
End Function
[/vba]

Working solution. excellent