Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cell
Hello
I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cell
I think you would need a formula, something like:
=Sheet1!A1&Sheet1!A2&Sheet1!A3 etc. Hope this helps. Pete On Dec 10, 7:23 pm, MDN wrote: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cell
have you tried to Concatenate
example: =Concatenate(A1,B2,C3) or =A1&B2&C3 "MDN" wrote: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cell
You could just combine using a formula on destination sheet.
=Sheet1!A1 & Sheet1!B1 and Sheet1!C1 etc. Or a User Defined Function....................... Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & " " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =Sheet1!(A1:A10) Or a macro............................. 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 Next z = Left(sbuf, Len(sbuf) - Len(w)) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 11:23:04 -0800, MDN wrote: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cell
try to use concatenate
assuming that you have one data base a b 1 Many to 2 Things do 3 4 =a1&" "&a2&" "&b1&" "&b2 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "MDN" escreveu: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cel
Thank you. Is is possible to Concatenate a large range of cells ? I have 75
cells that I want to put together. Does that mean I'll have to enter each number into the formula or is there a way to do a range? "Marcelo" wrote: try to use concatenate assuming that you have one data base a b 1 Many to 2 Things do 3 4 =a1&" "&a2&" "&b1&" "&b2 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "MDN" escreveu: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cel
Read my response.
Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 12:04:00 -0800, MDN wrote: Thank you. Is is possible to Concatenate a large range of cells ? I have 75 cells that I want to put together. Does that mean I'll have to enter each number into the formula or is there a way to do a range? "Marcelo" wrote: try to use concatenate assuming that you have one data base a b 1 Many to 2 Things do 3 4 =a1&" "&a2&" "&b1&" "&b2 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "MDN" escreveu: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying cell contents from many cells and pasting into one cel
Thanks!
"Gord Dibben" wrote: You could just combine using a formula on destination sheet. =Sheet1!A1 & Sheet1!B1 and Sheet1!C1 etc. Or a User Defined Function....................... Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & " " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =Sheet1!(A1:A10) Or a macro............................. 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 Next z = Left(sbuf, Len(sbuf) - Len(w)) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 11:23:04 -0800, MDN wrote: Hello I'd like to copy the contents from a group of cells and then paste all of those contents into one cell on a different worksheet. How can I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
prevent changes to cell references when copying and pasting | Excel Discussion (Misc queries) | |||
Copying and pasting table info and cell formating in one hit | Excel Discussion (Misc queries) | |||
Copying and Pasting a cell with a constant number | Excel Worksheet Functions | |||
Copying format to a new cell, w/o overwriting destination cell contents | Excel Discussion (Misc queries) | |||
If Statement and copying all of cell contents. | Excel Worksheet Functions |