copy specific columns from listbox to userform without selection

[Ali M]

Hello ,
I would copy specific columns from listbox to bottom in sheet without selected from listbox.
any idea to fix this code please?

Dim lastrow As Long
     With Sheet2
        .Range("A" & lastrow + 1).Value = Me.ListBox1.Column(0)
        .Range("B" & lastrow + 1).Value = Me.ListBox1.Column(1)
        .Range("C" & lastrow + 1).Value = Me.ListBox1.Column(3)
    End With

thanks

 

[dmt32]

well, I thought the first column in listbox takes zero !

That's correct but solution in link is slicing a 2D array so you start at Column 1

even if I change to 1 still shows error !

just tried it like this & works ok for me

  Dim c As Long
    With Me.ListBox1
     For c = 1 To 3
        Sheet2.Cells(1, c).Resize(.ListCount).Value = Application.Index(.List, 0, Choose(c, 1, 2, 4))
     Next c
    End With

Dave

 

[Ali M]

works ok for me

I'm really shocked!!
mismatch error

 Sheet2.Cells(1, c).Resize(.ListCount).Value = Application.Index(.List, 0, Choose(c, 1, 2, 4))

 

[dmt32]

I'm really shocked!!
mismatch error

 Sheet2.Cells(1, c).Resize(.ListCount).Value = Application.Index(.List, 0, Choose(c, 1, 2, 4))

perhaps if can share copy of your workbook (with dummy data) from a file sharing site like dropbox - would help forum to understand why you have the issue

Dave

 

[MikeVol]

Try next code:

    Dim i           As Long
    
    Dim lastrow      As Long
    lastrow = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row + 1

    With Me.ListBox1
        
        For i = 0 To .ListCount - 1
            Sheet7.Cells(lastrow, 1).Value = .List(i, 0)
            Sheet7.Cells(lastrow, 2).Value = .List(i, 1)
            Sheet7.Cells(lastrow, 3).Value = .List(i, 3)
            lastrow = lastrow + 1
        Next i

    End With

Sheet7 change in the name of your sheet

 

[Ali M]

here is file
Dropbox

 

[MikeVol]

Try:

    Dim c           As Long
    Dim r           As Long

    With Me.ListBox1

        For c = 1 To 3

            Dim destCol As Long
            destCol = Choose(c, 1, 2, 3)

            For r = 0 To .ListCount - 1
                sheet2.Cells(r + 1, destCol).Value = .List(r, Choose(c, 0, 1, 3))    ' Map ListBox columns to Sheet2
            Next r

        Next c

    End With

 

[dmt32]

Hi,
issue is way you are populating the listbox & likely to have some null vales which may be causing your error.
Have family with me today but unless another resolves for you, see if can find time to have a further look for you.

BTW as an aside

you can change this

Private Sub UserForm_Activate()
    Dim MonthName As Variant
    MonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    ComboBox1.List = MonthName
End Sub

to this

Private Sub UserForm_Activate()
    Me.ComboBox1.List = Application.GetCustomListContents(xlMonth)
End Sub

and if change xlMonth to xlDay you will get weekdays (mon, tue, wed etc)

Dave

 

[dmt32]

deleted

 

[Ali M]

thank mike
it works
last code will not copy to the bottom as previous.
will replace data inside sheet and this is not what I want.

 

[Ali M]

issue is way you are populating the listbox & likely to have some null vales which may be causing your error.

I'm not sure if I agree with you because the error doesn't occurs about combobox1 .