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

"Srikanth" wrote in message
m...
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