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??

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.
--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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??