Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
can you even use a reserved name like sheet1 to name a macro?
-- Gary "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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 "Gary Keramidas" wrote: can you even use a reserved name like sheet1 to name a macro? -- Gary "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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 ToolsMacro 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. -- Edmund (Using Excel XP) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
Don't know what to call what I need? | Excel Worksheet Functions | |||
Run or Call | Excel Programming | |||
call sub | Excel Programming | |||
How do you call one Sub from another Sub ? | Excel Programming |