Transfer listbox values to new worksheet

[bodiewil]

I know this should be simple but it's doing my head at he present.


I need to copy all the list box values to a new worksheet for reporting purposes. I can move the values but there seems to be a huge calculting time before the code finishes. I just simply need need to transfer the list box values until the last row is complete. It may be something to do with the rowsource which can be quite extensive that causes the long calculating time


This is the code I use :-

Private Sub CommandButton3_Click()

'saves listbox
Dim lItem As Long

'clear worksheet before saving
Sheets("newreps").Cells.Clear

For lItem = 0 To ListBox1.ListCount - 1
With Worksheets("newreps")

.Cells(lItem + 1, 1) = ListBox1.List(lItem, 0)
.Cells(lItem + 1, 2) = ListBox1.List(lItem, 1)
.Cells(lItem + 1, 3) = ListBox1.List(lItem, 2)
.Cells(lItem + 1, 4) = ListBox1.List(lItem, 3)
.Cells(lItem + 1, 5) = ListBox1.List(lItem, 4)

End With

Next lItem

MsgBox ("Transfer complete")


End Sub

Many thanks

Dave

[Norie]

Does this work any faster?

Please Login or Register  to view this content.