Move Userform Listbox selected items to another worksheet

[JayCo]

Hello,

I would like to create a Userform which requires the user to check through the items in a Listbox and click a command button to move the items to another sheet (Sheet2).

The Listbox Rowsource is a dynamic named range (=Sheet1!$A$2:INDEX(Sheet1!$1$65535,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

There are data contain in Sheet1 columns A to L.

I need a code for command button to move/copy selected items in the Listbox to Sheet2, then delete the same selected items in Sheet1 and refresh the Listbox, so that it appears relatively seamless to user.

Thanks!

-Jay

 

[Yongle]

This method works for me
- DynamicNamedRange and RowSource are NOT used
- ListBox MultiSelect property is set to fmMultiSelectMulti

USERFORM code

Private Sub CommandButton1_Click()
    Dim rng As Range, r As Long, ws1 As Worksheet
    Set ws1 = Sheets("Sheet1")
'get selected items
    Set rng = ws1.Cells(Rows.Count, 1)
    For r = 2 To ListBox1.ListCount + 1
        If ListBox1.Selected(r - 2) = True Then Set rng = Union(rng, ws1.Cells(r, 1))
    Next r
'copy & delete
    With Application
        .Calculation = xlCalculationManual: .ScreenUpdating = False
            rng.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            rng.EntireRow.Delete
        .Calculation = xlCalculationAutomatic: .ScreenUpdating = True: .CutCopyMode = False
    End With
    RefreshList
End Sub

Private Sub UserForm_Initialize()
    RefreshList
End Sub

Private Sub RefreshList()
    ListBox1.List = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Value
End Sub

 

[JayCo]

This method works for me
- DynamicNamedRange and RowSource are NOT used
- ListBox MultiSelect property is set to fmMultiSelectMulti

Thanks Yongle! It works perfectly.

Just 1 more question: How do i display the headers for the columns? I did select Listbox properties "ColumnHeads" to "TRUE" but it displays a blank row for the column heads.

 

[Yongle]

Try amending RefreshList like this and replace aRange with the name of your named range

Private Sub RefreshList()
    ListBox1.RowSource = "aRange"
End Sub

 

[JayCo]

Try amending RefreshList like this and replace aRange with the name of your named range

Private Sub RefreshList()
    ListBox1.RowSource = "aRange"
End Sub

It works perfectly, thank you so much!