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

  • Thread starter Thread starter christopher ward
  • Start date Start date
C

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")
 
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")
 
It's very rarely ever necessary to use Select or Activate
Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")
Click to expand...

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
 
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
 
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")
Click to expand...

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