Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy selected cells and paste the contents in just one cell using VBA

Posted on 2016-08-23
8
Medium Priority
?
69 Views
Last Modified: 2016-08-24
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
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41766828
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41766830
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41766839
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41767004
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
 
LVL 18

Expert Comment

by:xtermie
ID: 41767014
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41767023
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
 

Author Closing Comment

by:AndreasHermle
ID: 41767717
Great job ron, thank you very much
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41768354
No problems, glad to help.

Rob
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question