PDA

View Full Version : Solved: script to copy multiple cell values into one cell



Skopweb
09-24-2009, 11:21 PM
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
09-25-2009, 01:56 AM
Would it be OK to let it paste into multiple cells, then concatenate it?

Bob Phillips
09-25-2009, 02:01 AM
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
09-25-2009, 03:34 AM
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
09-25-2009, 04:03 AM
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
09-25-2009, 01:20 PM
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
09-26-2009, 04:01 AM
Something like


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

Skopweb
09-28-2009, 02:14 PM
hello vbax
should i enter this code in module,workbook or sheet

regards
skopweb

Bob Phillips
09-28-2009, 02:35 PM
That was standard code module.

Skopweb
09-28-2009, 11:45 PM
hello xld
this code is not working. i entered the code in module of excel

Regards
Skopweb

mdmackillop
09-29-2009, 12:20 AM
Cange it to a Sub
Select the cells in Excel you wish to join then run the sub

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

Skopweb
10-01-2009, 05:24 AM
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
10-01-2009, 05:58 AM
This will work on any worksheet change. I don't know that it can be limited to a Paste event


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

Skopweb
10-04-2009, 11:26 PM
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
10-05-2009, 12:18 AM
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.column > 2 then Exit Sub
Application.EnableEvents = False



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
10-05-2009, 04:03 AM
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
10-06-2009, 12:21 AM
Consider how you might change this line

If Target.column > 2 Then Exit Sub

Skopweb
10-13-2009, 03:09 AM
Thank you

If Not (Target.Column = 4) Then Exit Sub

Regards
Skopweb

mdmackillop
10-13-2009, 04:11 AM
... 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
02-06-2022, 03:53 AM
Something like


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


Working solution. excellent