• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

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
0
Andreas Hermle
Asked:
Andreas Hermle
  • 5
  • 2
1 Solution
 
xtermieCommented:
you can use a concatenate function to do that to
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);D15;CHAR(10);D16)

and you can copy the formula if your other data is in relative positions
0
 
Rob HensonFinance AnalystCommented:
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:
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

Open in new window


The code should be put in a Standard Module and can then be used on a sheet like a formula:

=AllComments(Range)

Thanks
Rob
1
 
Rob HensonFinance AnalystCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rob HensonFinance AnalystCommented:
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.

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

Open in new window

Thanks
Rob
0
 
xtermieCommented:
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
0
 
Rob HensonFinance AnalystCommented:
Slight update in order of statements, the previous version was adding a line break after the last entry.
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 - 1 Then Newline = Chr(10)
    If Count < LR Then
    Collated = Collated & Cell.Text & Newline
    Else
    Cells(R, C) = Collated
    Cells(R, C).Select
    End If
Next Cell
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Great job ron, thank you very much
0
 
Rob HensonFinance AnalystCommented:
No problems, glad to help.

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now