You cannot select a hidden sheet. To get the range

mycodearray = Sheets(leftstring).Range("C1:C5000")

OR

Dim myRange as Range
Set myRange = Sheets(leftstring).Range("C1:C5000")

If this post helps click Yes
---------------
Jacob Skaria


"christopher ward" wrote:

Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) - position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward