Worksheet name

[Ed]

I have the following code:

Set oWb = Workbooks.Open(fname)

oWb.Sheets("Test41").Range("A1:AQ100").Copy

What i need to do is substitute the sheet name("Test41")
with a value in list box I have created on the sheet. I
have tried this with a cell reference but i get the
messasge 'Subscipt out of range'

Any have any clever ideas??

 

[Tom Ogilvy]

Dim oWb as Workbook
Dim sh as Worksheet
Dim shName as String
Set oWb = Workbooks.Open(fname)

shName = oWb.Worksheets("Sheet1").Range("A1").Text
on Error Resume next
set sh = oWb.Worksheets(shName)
On error goto 0
if sh is nothing then
msgbox "No sheet named ->" & shName & "<-"
Else
sh.Range("A1:AQ100").Copy
End if

If the name is a valid sheet name, it should work.