Code to migrate the list box data to excel sheet

[saftawy1]

I need help with this code
Use this code to migrate the list box data to excel sheet and it works fine if the number of rows in the list box is small, but if the number of rows is more than 100 or 150, it is very slow in the migration
Do I find a solution to this?

[QUOTE]
Dim z As Integer
Dim v As Integer
   Application.ScreenUpdating = False

    With ThisWorkbook.Sheets("report2").Range("b5:s2000").ClearContents

        z = 5
        For v = 0 To ListBox1.ListCount
        On Error Resume Next
            ThisWorkbook.Sheets("report2").Cells(z, 2).Value = CDate(ListBox1.List(v, 0))
            ThisWorkbook.Sheets("report2").Cells(z, 3).Value = CDate(ListBox1.List(v, 1))
            ThisWorkbook.Sheets("report2").Cells(z, 4).Value = CDate(ListBox1.List(v, 2))
            ThisWorkbook.Sheets("report2").Cells(z, 5).Value = ListBox1.List(v, 3)
            ThisWorkbook.Sheets("report2").Cells(z, 6).Value = ListBox1.List(v, 4)
            ThisWorkbook.Sheets("report2").Cells(z, 7).Value = ListBox1.List(v, 5)
            ThisWorkbook.Sheets("report2").Cells(z, 8).Value = ListBox1.List(v, 6)
            ThisWorkbook.Sheets("report2").Cells(z, 9).Value = ListBox1.List(v, 7)
            ThisWorkbook.Sheets("report2").Cells(z, 10).Value = ListBox1.List(v, 8)
            ThisWorkbook.Sheets("report2").Cells(z, 11).Value = ListBox1.List(v, 9)
            ThisWorkbook.Sheets("report2").Cells(z, 12).Value = ListBox1.List(v, 10)
            ThisWorkbook.Sheets("report2").Cells(z, 13).Value = ListBox1.List(v, 11)
            ThisWorkbook.Sheets("report2").Cells(z, 14).Value = ListBox1.List(v, 12)
            ThisWorkbook.Sheets("report2").Cells(z, 15).Value = ListBox1.List(v, 13)
            ThisWorkbook.Sheets("report2").Cells(z, 16).Value = ListBox1.List(v, 14)
            ThisWorkbook.Sheets("report2").Cells(z, 17).Value = ListBox1.List(v, 15)
            ThisWorkbook.Sheets("report2").Cells(z, 18).Value = ListBox1.List(v, 16)
            ThisWorkbook.Sheets("report2").Cells(z, 19).Value = ListBox1.List(v, 17)
            z = z + 1
        Next
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True
[/QUOTE]

 

[Domenic]

Try the following instead . . .

    With Me.ListBox1
        ThisWorkbook.Sheets("report2").Range("B2").Resize(.ListCount, .ColumnCount).Value = .List
    End With

Does this help?

 

[Domenic]

Actually, since you want to format the first three columns of the transferred data as a date, try...

    Dim wsDest As Worksheet
    Set wsDest = ThisWorkbook.Sheets("report2")
    
    With Me.ListBox1
        wsDest.Range("B2").Resize(.ListCount, .ColumnCount).Value = .List
    End With
    
    With wsDest
        .Range("B2:D" & .Cells(.Rows.Count, "B").End(xlUp).Row).NumberFormat = "mm/dd/yyyy"
    End With

Change the format as desired.

 

[saftawy1]

Actually, since you want to format the first three columns of the transferred data as a date, try...

    Dim wsDest As Worksheet
    Set wsDest = ThisWorkbook.Sheets("report2")
   
    With Me.ListBox1
        wsDest.Range("B2").Resize(.ListCount, .ColumnCount).Value = .List
    End With
   
    With wsDest
        .Range("B2:D" & .Cells(.Rows.Count, "B").End(xlUp).Row).NumberFormat = "mm/dd/yyyy"
    End With

Change the format as desired.

thank you very much its doing excellent

 

[Domenic]

That's great, glad I could help.

Cheers!