Call

[Guest]

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

 

[Guest]

Hi Edmund,

What you're doing is not proper syntax. For example, if the active sheet's
name is "Sheet1" then using the string variable like you are means you're
actually doing this:

Call "Sheet1"

when what you want is:

Call Sheet1

You need to implement a select case structure so you can redirect based on
the sheetname. ..Just one way to go!

HTH
Regards,
GS

 

[Tim Williams]

Try

Application.Run ActiveSheet.Name

Tim

 

[Gary Keramidas]

can you even use a reserved name like sheet1 to name a macro?

--


Gary

Try

Application.Run ActiveSheet.Name

Tim

 

[Guest]

Hi Gary,

Yes, you can. Try this and see what happens.

Sub Sheet1()
Debug.Print "this works"
End Sub

Sub test()
Call Sheet1
End Sub

Regards,
Garry

can you even use a reserved name like sheet1 to name a macro?

 

[Guest]

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

Try

Application.Run ActiveSheet.Name

Tim

 

[Guest]

Hello

Let me tell u my full scenario. Perhaps u can suggest how can I achieve it.

At work, I download query reports (database) into Excel file. Everyday, I hv
to download 5 reports. (Note : I store all the 5 downloaded reports in 5
separate Excel sheets but they are all under 1 single Excel file).

What made life difficult was that the reports are haphazardly formatted &
delimited(with lots of spaces & weird random formatting). Instead of spending
time daily in adjusting manually for each sheet, I attempted to compile 5
different macro procedures to rectify each sheet's data.

Let's say:
Sheet1 hold data for Purchase Price
Sheet2 holds Vendor Information
Sheet3 holds Contract Information
Sheet4 holds Overdue P/O
Sheet5 holds Outstanding Production Backlog

My 5 macros are tailored to cater for rectification for each sheet. But to
select & press the button in Tools>Macro to execute the codes daily, risks
accidental execution. To make life easier, I'm tyring to derive a macro that
will "take the active sheet's name so as to use that name as the guide for it
to grab the correct procedure to execute". That's why in VBE , procedure
inside "Sub Sheet1()" is intended to rectify Sheet1's inconsistency".

In short, I just need a procedure that will take the name of the active
sheet, & use that name to trigger the right macro that is intended for that
specific sheet. When this is successful, I can rest my worries as I only need
to run 1 single macro, where this macro will trigger the execution of the
right procedure of the 5 procedures in hand.

I'm a VBA rookie. Very very raw to VBA.

 

[Guest]

Hi Edmund,

It's a good thing you currently only have 5 sheets to "worry" about. Adding
more sheets will be a simple matter of writing each one's procedure, and
adding a call statement to it. That said, constructing the calling procedure
is fairly simple.

How I suggest you handle it is by looping through each sheet in the workbook
and redirecting the code to the procedure associated with that sheet. You can
use its name to do this just like you want, using a Select Case structure. I
strongly suggest you use other names for the sheets. Something that
associates their nature would be more appropriate. For example:

PurchasePrice
VendorInfo
ContractInfo
OverduePO
ProductionBacklog

Here's how:

Sub FormatSheets()
' This loops through the sheets in the ActiveWorkbook,
' to call a procedure according to each sheet's name.

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case Is = "PurchasePrice": Call FormatPurchasePrice
Case Is = "VendorInfo": Call FormatVendorInfo
Case Is = "ContractInfo": Call FormatContractInfo
Case Is = "OverduePO": Call FormatOverduePO
Case Is = "ProductionBacklog": Call FormatProductionBacklog
'Insert new sheets here as required
End Select
Next wks

End Sub

You can put this in a standard module along with the called procedures for
each sheet.

HTH
Regards,
GS