Copy from many cells and paste to one

[eoreality]

I need to copy a range of cells (C7:C20) and past the contents into one cell (B4). the concatenate function is not the solution because I need the results to appear as separate lines in the cell (B4) when pasted. (leaving a "return" between lines)

Weird I know but Ahem, "It's for a friend".


Thanks,

Robert

[Dave Peterson]

J.E. McGimpsey has a User defined function that will concatenate cells.

You can find it at:
http://www.mcgimpsey.com/excel/udfs/multicat.html

You'll use it in the worksheet like:

=MultiCat(c7:c20,char(10))

J.E. shows this as his example: =MultiCat(A1:C1," ")

Char(10) is that alt-enter that you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

eoreality wrote:
>
> I need to copy a range of cells (C7:C20) and past the contents into one
> cell (B4). the concatenate function is not the solution because I need
> the results to appear as separate lines in the cell (B4) when pasted.
> (leaving a "return" between lines)
>
> Weird I know but Ahem, "It's for a friend".
>
> Thanks,
>
> Robert
>
> --
> eoreality
> ------------------------------------------------------------------------
> eoreality's Profile: http://www.excelforum.com/member.php...o&userid=24549
> View this thread: http://www.excelforum.com/showthread...hreadid=381448

--

Dave Peterson

[Gord Dibben]

Robert

You could do this with a formula.

=C7 & CHAR(10) & C8 & CHAR(10) etc. until C20.

Or you could use a macro to do it.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w & Chr(10)
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

You probably don't need a delimiter in your case, so don't enter anything in
that inputbox.


Gord Dibben Excel MVP

On Wed, 22 Jun 2005 18:25:28 -0500, eoreality
<eoreality.1r1ynb_1119485165.7059@excelforum-nospam.com> wrote:

>
>I need to copy a range of cells (C7:C20) and past the contents into one
>cell (B4). the concatenate function is not the solution because I need
>the results to appear as separate lines in the cell (B4) when pasted.
>(leaving a "return" between lines)
>
>Weird I know but Ahem, "It's for a friend".
>
>
>Thanks,
>
>Robert