Copying different cells from one list to another

I

Imabrit

If anyone can advise me I would greatly appreciate your help.

I have to take individual number from a large list and put them into a
smaller list of their own.

I can highlight my list but when I go to copy them I get the message:

'That command cannot be used on multiple selections'

There must be a way to do this rather than individually copy and paste each
number right?

Many thanks in advance!
Jackie
 
B

Billy Liddel

Imabrit said:
If anyone can advise me I would greatly appreciate your help.

I have to take individual number from a large list and put them into a
smaller list of their own.

I can highlight my list but when I go to copy them I get the message:

'That command cannot be used on multiple selections'

There must be a way to do this rather than individually copy and paste each
number right?

Many thanks in advance!
Jackie
Click to expand...

I'm afraid not, Excel will not copy more than one range. However you can get
around this with VBA (A MAcro).

The cells in the first column below were selected (ctrl + click) and
importantly the first cell in the destination column. Then the macro placed
the selected values into a list beginning with that empty cell.

a1 a1
a2 a3
a3 a5
a4 a7
a5
a6
a7

the macro is

Sub CopyCellsToList()
Dim c, mystr As String, count As Integer
Dim row As Long, i As Long, col As Integer, data
count = 0
'The active cell is the last cell selected
row = ActiveCell.row
col = ActiveCell.Column
'Get the data to copy
For Each c In Selection
If count = 0 Then
mystr = c & ","
ElseIf IsEmpty(c) Then
mystr = mystr
Else
mystr = mystr & c & ","
End If
count = count + 1
Next
'write the data
data = Split(mystr, ",")
For i = LBound(data) To UBound(data)
Cells(row, col) = data(i)
row = row + 1
Next i

End Sub

Press ALT + F11 keys to open the VB Editor. Choose Insert, Module and paste
in the code.

Return to your sheet and select the cells including the destination cell and
run the code. Perss ALT + F8 keys select the macro and choose Run.

Regards
Peter
 
I

Imabrit

Hey Peter:

Guess what...I've been playing around with this and if you hold down CTRL
button on the first number of the 'main' list of numbers BUT DON'T CLICK ON
THE FIRST CELL you can pick up THAT cell and others that are needed...then
right click, copy and paste...it works!! Eureka!

Thanks anyway darlin'
Much appreciated.
 
Top