RE: Getting data from a worksheet

[Barb Reinhardt]

Sure you can.

Trythis

Dim myWS as Excel.Worksheet

Set myWS =Worksheets("Data")

Anyplace where you are referencing a range object on the Data sheet, use this

myWS.Range ...

HTH,
Barb Reinhardt

"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
>
>
>
>
>
>

[Barb Reinhardt]

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]

[Per Jessen]

Sure, either use a "with" statement or use the worksheet reference
each time the macro access a cell on the Data Sheet or use the
worksheet reference in each sheet. I prefer to use a variable to hold
the worksheet reference, but it can also be done with Worksheets
("Data"),Range("A" & ....

See my two examples.

Sub aaa()
'Note the leading dots in the range and cells statements which
' indicate the reference to Worksheets("Data")

Dim MyArray(40, 40)
With Worksheets("Data")
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

End With
ListBox1.List() = MyArray
End Sub

Sub bbb()
Dim ws As Worksheet
Dim MyArray(40, 40)
Set ws = Worksheets("Data")
For i = 2 To ws.Cells(ws.Cells.Rows.Count, "A").End(xlUp).Row - 1
X = ws.Range("A" & i).Value
If Mid(ws.Range("C" & i), 5, 1) = "(" Then
Y = Left(ws.Range("C" & i), 7)
GoTo Line1
End If
Y = Left(ws.Range("C" & i), 3)

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

End With
ListBox1.List() = MyArray
End Sub

Hopes this helps.

---
Per

[donwb]

Many thanks Barb & Per.
Both solutions worked for me & solved the problem.
donwb

"donwb" <2ban...@tiscali.co.uk> wrote in message
news:%231k1gIv...@TK2MSFTNGP02.phx.gbl...