sheet reference

[Srikanth]

I want to do the following:

(say) I have an excel file by name XYZ.xls and this file has many
worksheets in it. But I am interested in a specific sheet with name
"XYZ all". How to reference the sheet when the filename XYZ could be
different every time I run the macro ? Please help. Thanks

Srikanth

 

[Tom Ogilvy]

Dim bk as Workbook, bk1 as Workbook
Dim sh as Worksheet
set bk1 = Nothing
for each bk in Application.Workbooks
set sh = Nothing
On error resume next
set sh = bk.worksheets("XYX all")
On Error goto 0
if not sh is nothing then
set bk1 = bk
exit for
End if
Next

if not bk1 is nothing then
msgbox "XYX all found in " & bk1.name
End if

 

[Otto Moehrbach]

I read your question that you want to reference a sheet name that consists
of the following:
The file name without the ".xls" followed by:
A space, followed by:
"all"

Is that what you want?
If so, then something like this might help.
Dim ShtName As String
ShtName=Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)-4)
ShtName = ShtName & " all"

Or maybe I didn't read your question right.
HTH Otto