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

 

[Ali M]

Hi Mike,
can you tell me why start copying from row2 , not row1 ?!

 

[MikeVol]

Hi @Ali M.
The problem why data copying starts from row 2 and not from row 1 is related to the definition of the lastrow variable in the CommandButton1_Click procedure. In order for the macro to handle cases where the target sheet (Sheet2) has no data (i.e. lastrow = 0), we need to properly initialize lastrow to the first row. Additionally, we need to dynamically update lastrow on each execution to prevent overwriting existing data. Here is the updated code:

    Dim i           As Long

    With sheet2

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

        If lastrow = 1 And .Cells(1, 1).Value = "" Then
            lastrow = 1
        Else
            lastrow = lastrow + 1
        End If

    End With

    With Me.ListBox1

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

    End With