As an example, I'd change

For i = 2 To Cells(Cells.Rows.Count, "A").End(xlUp).Row - 1

to

For i = 2 To myws.Cells(myws.Rows.Count, "A").End(xlUp).Row - 1

and

X = Range("A" & i).Value

to

X = myWS.Range("A" & i).Value


"donwb" wrote:

Is there any way of getting data from a WorkSheet
without actually selecting it.
an extract of my code is:-

Worksheets("Data").Activate
Dim MyArray(40, 40)
For i = 2 To Cells(Cells.Rows.Count, "A").End(xlUp).Row - 1
X = Range("A" & i).Value

If Mid(Range("C" & i), 5, 1) = "(" Then
Y = Left(Range("C" & i), 7)
GoTo Line1
End If

Y = Left(Range("C" & i), 3)

Line1:
MyArray(i - 2, 0) = X
MyArray(i - 2, 1) = Y

Next i
ListBox1.List() = MyArray

What I'm trying to avoid is the screen changing from a particular WorkSheet
to the "Data" worksheet, when the code is run
donwb