View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.misc
Tigerxxx Tigerxxx is offline
external usenet poster
 
Posts: 78
Default Copy multiple cells to one cell

Thanks a lot for your help guys!

Hi Max,
The macro will ber very helpful!

Thanks!

"Max" wrote:

"Tigerxxx" wrote:
Cells A1=1, B1=2, C1=3......Y1=25, Z1=26
I want to copy the data in cells A1,B1 through Z1 into one single cell A2
i.e. I want to copy 26 cells into one single cell.


Another option - try this sub by Gord Dibben

Sub ConCat_Cells()
'Gord Dibben .misc
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) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---