Make copy from a serial cells to one cell

[Etphonehome]

Is there a way that can copy a serial cells value to one cell ?

Thanks
Et

 

[VoG]

Can you explain what you mean in more detail.

 

[Richard Schollar]

Do you mean concatenate the values in several cells into one cell like this:

=A1 & A2 & A3 & A4 & A5 & A6

 

[Etphonehome]

I need to copy the value from groups of cells manually to one cell as the source cells don't have a consistent pattern, using & wouldn't save me much time.

Example :
Source
A1 345-678
A14 564-234
A67 453-098

Destination
H78. 345-678
564-234
453-098

I will use alt+enter to separate them to different line in H78. I can use filter to get the source with same type source then make copy. This is a mapping job for a report setting, seems no shortcut i will have but using filter and my eye to collect the source. I got many of these to copy, so just want to ask for help if there is a faster way to do the copy job. thanks for your help.

Et

 

[Richard Schollar]

You could use a simple macro and activate it from a keypress (eg Ctrl+q):

Sub Concat_Cells()
Dim c As Range, temp As Variant

On Error Resume Next

For Each c In Application.InputBox("Select your cells",Type:=8).Cells
  temp = temp & Chr$(10) & c.Value
Next c

ActiveCell = Mid$(temp,2)

End Sub

If you place this in a module in your workbook (via Alt+F11 to open the VBE and then Alt+I+M to insert a module - paste in here), then go back into Excel and in 2007 or 2010 * Developer tab>Macros>Select Concat_Cells from the list and hit Options to assign a shortcut key (eg Ctrl+q). Now to activate you just need to select the cell where you want the concatenated data to go and hit Ctrl+q to activate a dialog where you can select the cells (hold down Ctrl so all cells selected) you want, hit OK and the results will be placed in the cell.

* in 2003 or earlier it is in Tools>Macros

 

[Etphonehome]

You could use a simple macro and activate it from a keypress (eg Ctrl+q):

Sub Concat_Cells()
Dim c As Range, temp As Variant

On Error Resume Next

For Each c In Application.InputBox("Select your cells",Type:=8).Cells
  temp = temp & Chr$(10) & c.Value
Next c

ActiveCell = Mid$(temp,2)

End Sub

If you place this in a module in your workbook (via Alt+F11 to open the VBE and then Alt+I+M to insert a module - paste in here), then go back into Excel and in 2007 or 2010 * Developer tab>Macros>Select Concat_Cells from the list and hit Options to assign a shortcut key (eg Ctrl+q). Now to activate you just need to select the cell where you want the concatenated data to go and hit Ctrl+q to activate a dialog where you can select the cells (hold down Ctrl so all cells selected) you want, hit OK and the results will be placed in the cell.

* in 2003 or earlier it is in Tools>Macros

Richard, Thank you so much for your help. I am now not at my computer will try it later.

 

[Etphonehome]

Richard, Thank you so much for your help. I am now not at my computer will try it later.

Richard, thanks so much for the help. I am now not with my computer, will try it later.