Copying and pasting items in a listbox on a user form to a worksheet

[KimberlyC]

Hi

I'm using the following code to copy the contents of a listbox (user selects
the listed items to be copied) in a user form to a range in a worksheet:
(The listbox is populated from data in a worksheet)

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer

With listbox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Range("codestosummary").Offset(j, 0).Value = .List(i)
j = j + 1
End If
Next i
End With
Unload CodesToSummaryForm


End Sub

When I run this code.. I would like to have the contents copied to the
first cell empty in Col. B ..starting with cell B14... all the way down to
B49..
So.. if the user selects 3 items to be copied in the listbox... and cells
B14 through B20 have data in them... then those listbox items would be
pasted into cell B21, B22, and B23.

I do not know how to do this...
Any help would be greatly appreciated..
Thanks in advance..
Kimberly

 

[Bob Flanagan]

Kimberly, try something like this:

dim cell as range
set cell = cells(65536,2).end(xlup).offset(1,0)
With listbox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
cell.value= .List(i)
set cell = cell.offset(1,0)
End If
Next i
End With

The statement cells(65536,2).end(xlup) is the same as going to the last cell
in the column and pressing the End key and then the up arrow. It takes you
to the first cell with an entry above. This would be the last entry in the
column. The .Offset(1,0) says to set cell t the cell one row below this
cell.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Kim Chiaramonte]

Hi Bob..

Thanks!!

That worked..but I need to have the items from the listbox copied to a
workhseet called Summary and the first cell that the pasting should
begin in is B14 on the Summary worksheet.
How do I add this to the code you sent??
Thanks so much for your help!