Do you want 500 items from 10 sheets (total 5,000 items) added to the list box.?
If so this code uses Sheet1 as working area, builds a 5,000 cell range and then populates the listbox. Assumes a control toolbox listbox.
Code
Option Explicit
Sub PopulateListBox()
Dim ws As Worksheet
Dim Row As Long
Dim arData
Row = 1
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then
Row = Row + 500
ws.Range("A1:A500").Copy Sheet1.Cells(Row, 1)
End If
Next ws
arData = Sheet1.Range("A1:A5000")
ListBox1.List = arData
Sheet1.Range("A501:A5000").ClearContents
End Sub
Display More