Andreas Hermle
asked on
Copy selected cells and paste the contents in just one cell using VBA
Dear Experts:
I would like to run an Excel Macro that performs the following actions:
Select all the cells which need to be copied into the clipboard.
Paste the whole contents into just one cell (5 cells to the right relative to the first cell that gets copied) whereby the individual cell entries get separated by a manual line break (Alt+Enter)
I have attached a sample file for your convenience.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
copy_selected_cells_and_paste_conte.xlsx
I would like to run an Excel Macro that performs the following actions:
Select all the cells which need to be copied into the clipboard.
Paste the whole contents into just one cell (5 cells to the right relative to the first cell that gets copied) whereby the individual cell entries get separated by a manual line break (Alt+Enter)
I have attached a sample file for your convenience.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
copy_selected_cells_and_paste_conte.xlsx
I have done similar with a User Defined Function (UDF) which is basically VBA that gets used in the same way as a formula.
The code is:
The code should be put in a Standard Module and can then be used on a sheet like a formula:
=AllComments(Range)
Thanks
Rob
The code is:
Public Function AllComments(Rng As Range)
R = 0
LR = Rng.Cells.Count
For Each Cell In Rng
Newline = ""
R = R + 1
If R < LR Then Newline = Chr(10)
Collated = Collated & Cell.Text & Newline
Next Cell
AllComments = Collated
End Function
The code should be put in a Standard Module and can then be used on a sheet like a formula:
=AllComments(Range)
Thanks
Rob
Just been testing and it seems to only work on a continuous range so might not be suitable for what you want. Might be able to tweak it for individual cells.
In addition, to see the effect of the Line Breaks the cell has to have Wrap Text formatting enabled.
Thanks
Rob H
In addition, to see the effect of the Line Breaks the cell has to have Wrap Text formatting enabled.
Thanks
Rob H
Updated and converted to a macro. Select the cells containing the text and then the destination cell using Ctrl + Click then run the macro. The concatenated values will go in the last cell selected.
Rob
Sub Collate()
Count = 0
LR = Selection.Cells.Count
For Each Cell In Selection
R = Cell.Row
C = Cell.Column
Newline = ""
Count = Count + 1
If Count < LR Then
Newline = Chr(10)
Collated = Collated & Cell.Text & Newline
End If
Next Cell
Cells(R, C) = Collated
Cells(R, C).Select
End Sub
ThanksRob
if you decide to go with the macro rather than the formula you may want to assign the macro to a custom button on the ribbon or the quick access toolbar so that it is easy to run the macro
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great job ron, thank you very much
No problems, glad to help.
Rob
Rob
for example in the target cell (5 cells right from D14) you enter
= D14& Char(10)&D15& Char(10)& D16
OR
=CONCATENATE(D14;CHAR(10);
and you can copy the formula if your other data is in relative positions