Copy from Listbox to empty sheet

[BigWes1960]

Hi all, thanks for being here.

I have a worksheet with a Listbox on a Userform. I have made the Listbox a method of selecting items from the worksheet. The Listbox is populated with items from Column A, with a checkbox in the Listbox (multiselect).

I need to allow the user to select an item (or items) in the Listbox, and then place those selections into a new sheet (or another area on the same sheet) for eventually printing the selections made.

I just can't figure out how to reference the selections made in the Listbox, identify them, and put them into the next row.:banghead: :banghead: :banghead:

For example, I have a list of 8000 comic books that a user can scroll through. He selects 20 of them from various rows on the comic book spreadsheet (in the Listbox). When he clicks "okay" (or whatever), I want to place that list of 20 comic books in column A starting at row 1 of a new sheet. Then I can print this new sheet/list and give it to a picker who gets the items from inventory.

Thanks in advance! :bow:

[Kenneth Hobs]

Welcome to the forum!

See the attachment or review the code.

Private Sub UserForm_Initialize()
Dim r As Range
Set r = Worksheets("ComicBooks").Range("A1", Worksheets("ComicBooks").Range("A1").End(xlDown))
ListBox1.List = WorksheetFunction.Transpose(r)
End Sub

Private Sub CommandButton1_Click()
Dim w As Worksheet, c As Range, cbn As Long
'Set w = Worksheets.Add
Set w = ActiveSheet

Set c = w.Range("A1")
c.Value2 = TextBox1.Value

With ListBox1
For cbn = 0 To .ListCount - 1
If .Selected(cbn) = True Then
Set c = c.Offset(1)
c.Value2 = .List(cbn)
End If
Next cbn
End With

With w
.Columns("A:A").AutoFit
.UsedRange.PrintOut
.UsedRange.Clear
End With

Unload Me
End Sub

[BigWes1960]

Welcome to the forum!
...



Kenneth, thanks for the welcome and the quick reply (and the code).

I copied the code, and ran it. I keep getting "Subscript out of range".

It seems that the line "Set r = Worksheets..." is where it's breaking. I "mostly" understand what it is you're doing with the code, but I go lost in that line, too.

Am I overlooking something in my naming conventions or something?

Thanks again!

[Kenneth Hobs]

Most likely you do not have a sheet named, ComicBooks. Put your own sheet name there. In the example file, I hide that sheet.

[BigWes1960]

Thanks! Got it.