Retrieve all the text from a user selection

[tittatty]

I currently have a project using Excel 2007 where I need to allow the user to make a selection of cells (no limit) and then I need to copy of the contents of those selected cells into one cell on a different sheet. I have most of it accomplished but I am stuck on how to retrieve all the text from the selection if the selection is over multiple cells.

I try to use:
Dim selText as String
selText = Selection.Text

But this only works if only one cell is selected, otherwise I get an error. I know there has to be an easy way to do this...Any help is much appreciated.

 

[PHV]

A starting point:

Dim s As String, c As Range
For Each c In Selection
  s = s & c.Text
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tittatty]

Thanks! Works perfect.

 

[SkipVought]

FYI,

Please note that COPY refers to using the CLIPBOARD in a COPY and PASTE method.

You are not doing any COPY. Rather you are concatenating values in a selected range to a string, that will, in turn, be assigned to a cell value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}