Select a Sheet Issue in VBA on a very hidden sheet - fails

[christopher ward]

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

 

[john]

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

 

[Peter T]

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

 

[Jacob Skaria]

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

 

[ryguy7272]

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

http://www.teachexcel.com/macros/delete_hidden_worksheets.ph
http://stackoverflow.com/questions/...re-possible-on-hidden-worksheets-or-workbooks


HTH,
Ryan--

 

[NoNickName]

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

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.

 

[Patrick Molloy]

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

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