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

You can't select a hidden sheet and in most case you don't need to do this
anyaway

Try changing this:

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


to this

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


"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

It's very rarely ever necessary to use Select or Activate

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

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

Maybe first assign the worksheet to an object variable which you can use
repeatedly until it goes out of scope, eg

Dim ws as Worksheet
Set ws = ActiveWorkbook.Worksheets(leftstring)
mycodearray = ws.Range("C1:C5000")

Regards,
Peter T


"christopher ward" wrote in
message ...
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

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

Unhide the sheet! This may give you some ideas of how ot get started:
http://excel.tips.net/Pages/T002548_...orksheets.html

http://www.teachexcel.com/macros/del...worksheets.php
http://stackoverflow.com/questions/8...s-or-workbooks


HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"john" wrote:

You can't select a hidden sheet and in most case you don't need to do this
anyaway

Try changing this:

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


to this

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


"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

On Jul 21, 10:47*am, 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

A bit long-winded but have you tried turning off screen updating,
unhiding the worksheet, doing your processing and then hiding again
before turning screen updates back on? That way any hidden information
remains unhidden as far as the user can see.

very rarely will you need to selecta cell to read/write data
try

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

"christopher ward" wrote in
message ...
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