Hi Tim,

< Application.Run ActiveSheet.Name
The data type of a sheetname is String, therefore yields the "The macro
'Sheet1' cannot be found." error.

Call ActiveSheet.Name
returns the "Object doesn't support this property or method." error.


This:
Dim sName As Variant
sName = ActiveSheet.Name
Call sName
returns the "Compile Error: Expected Sub, Function, or Property" message.

Regards,
Garry

"Tim Williams" wrote:

Try

Application.Run ActiveSheet.Name

Tim


"Edmund" wrote in message
...
Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet
openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to
call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can
recognize
the right name in order to invoke CALL the the sub procedure named
"Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or
Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)