Is it possible to Run a certain macro by the activehseets tab name?

[zone709]

Hi,

I want to know if I can run certain macro if the tab name lets say is "data" or "data2()

So if my active sheet I am using the tabs name can be either "data" or "data2" right.

1st macro is if the first tab name is "Data" then it runs this macro

Sub MacroData ()

If the next time I open the sheet and the first tab name is called "Data2" then it would run

Sub MacroData2 ()

So basically I run the macro right it would look at the tab name. Which would be "data" or "data2" then it would choose what macro to run. Is this possible if so I can use some help.

Any help thanks.

 

[L. Howard]

Ok I will try this one also. One last thing I want to ask that came up. Lets say I want to run your code or this and I'm not on the activesheet. What do I change so iif I'm on tab 2 or 3 that I can run it and it will always look at the first tab.

Hmm, not sure what you are asking. If you "are not on the activesheet" then where are you??

So, as you say, "if I'm on tab 2 or 3..." then tab2 or 3 is the active sheet. Maybe try a version of this from post #4 Application.Run "Macro" & Sheets(1).Name

This may be even more confusing, but give it a try. I may be confused my own self.

Copy the codes below to the five sheet test workbook and into a standard module.

Rename Sheet1 to "Test", so now as you look at the sheets, you have five sheets named Test, Sheet2, Sheet3, Sheet4, HorseFeathers.
Look into the Project Explorer in the VB Editor and see that:
Sheet1 is named 'Test'
Sheet2 is named 'Sheet2'
Sheet3 is named 'Sheet3'
Sheet4 is named 'Sheet4'
Sheet5 is named 'HorseFeathers'.

Run sub My_Test from any one of the five sheets.
My_Test will call the EXCEL sheet1 (which you renamed "Test") and the EXCEL sheet3 which is named Sheet3.

You said "...to always look at the first tab." but I added the sheet3 call in there as an additional demo/info. The macro My_Test calls Excel sheets 1 & 3 regardless of the name you have on them as you look at them from the worksheet.

Howard

Sub My_Test()
Application.Run "Macro" & Sheets(1).Name
Application.Run "Macro" & Sheets(3).Name
End Sub

Sub MacroTest()
Dim nme As String
nme = ActiveSheet.Name
MsgBox "The macro named MacroTest was called from sheet " _
        & """" & nme & """" & vbCr & " and you are viewing this from that sheet." _
        & vbCr & " See sheet ""Test"" for results."
Sheets(1).Range("A" & Rows.Count).End(xlUp)(2) = Date & " " & "called from sheet - " & " " & nme
End Sub


Sub MacroSheet3()
Dim nme As String
nme = ActiveSheet.Name
MsgBox "The macro named MacroSheet3 was called from sheet " _
        & """" & nme & """" & vbCr & " and you are viewing this from that sheet." _
        & vbCr & " See sheet ""Sheet3"" for results."
Sheets(3).Range("A" & Rows.Count).End(xlUp)(2) = Date & " " & "called from sheet - " & " " & nme
End Sub

 

[zone709]

hi i hope im not confusing you. I would like to stick with this casue its working fine, but I think I need to change this part below ActiveSheet.Name because I need to look for Data or Data 2 in the first tab. If I try to run this in another tab like sheets2 named "Time". Hope this helps

Sub Sheets_Macro_Call()
  Dim ws As Worksheet
    
  With ws
    If ActiveSheet.Name = "[COLOR=#ff0000]One_Worksheet[/COLOR]" Then     <----- Maybe change to sheets1
        Call MacroTimeRetail
        
       ElseIf ActiveSheet.Name = "[COLOR=#ff0000]Other_Worksheet[/COLOR]" Then   <----- Maybe change to sheets1
        Call MacroTimeRetail2
        
    End If
  End With
   
End Sub
 

Sub MacroTimeRetail() 'Adding Timesheet'
 Dim ActiveWBName As String
 Dim WB As Workbook
 ActiveWBName = ActiveWorkbook.Name
 ChDir "C:\Timesheets\"
 fname = Application.GetOpenFilename
 If fname <> False Then
 Set WB = Workbooks.Open(fname)
 Sheets("Data").Select
 Sheets("Data").Move Before:=Workbooks(ActiveWBName).Sheets(1)
 For Each sht In Workbooks(ActiveWBName).Sheets
 sht.Cells.Replace What:="Data", Replacement:="'Data (2)'", LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False
 Next sht
 
Application.DisplayAlerts = False
Sheets("Data").Delete
Application.DisplayAlerts = True
        End If
End Sub

Sub MacroTimeRetail2() 'Adding Timesheet 2'
 Dim ActiveWBName As String
 Dim WB As Workbook
 ActiveWBName = ActiveWorkbook.Name
 ChDir "C:Timesheets\"
 fname = Application.GetOpenFilename
 If fname <> False Then
 Set WB = Workbooks.Open(fname)
 Sheets("Data").Select
 Sheets("Data").Move Before:=Workbooks(ActiveWBName).Sheets(1)
 For Each sht In Workbooks(ActiveWBName).Sheets
 sht.Cells.Replace What:="'Data (2)'", Replacement:="'Data'", LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False
 Next sht
 
Application.DisplayAlerts = False
Sheets("Data (2)").Delete
Application.DisplayAlerts = True
        End If
End Sub

 

[zone709]

I mean if i try to run this let's say in a tab called Time which would be second tab. It won't work because it's probably looking for data in that activesheet that i'm in called time. Appreciate all the help i really hope i didn't confuse you.

 

[L. Howard]

I mean if i try to run this let's say in a tab called Time which would be second tab. It won't work because it's probably looking for data in that activesheet that i'm in called time. Appreciate all the help i really hope i didn't confuse you.

If you need to do stuff to the sheet named Data, you should be able to call a macro from any sheet to act upon the sheet named Data, regardless where sheet Data in in the sheet tab order.

If the macro to do the stuff to sheet Data is also required to do stuff to the activesheet, then that is okay also. The code will need to be specific to the sheet it is acting upon. There is seldom a need to actually SELECT the sheet to have the code effectively do its work on a given sheet.

And if you are going to loop through all the sheets (as it appears you do) and you want to exclude sheet Data from that loop, then that is possible also.

a non-working example...

[I][B]Sub aName_Whatever()
[/B]
[B]For each sheet in workbook

  If sht.Name <> Data then 
      sht.Cells.Replace What:="Data", Replacement:="'Data (2)'", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False  
  
  End If

Next

With Sheets("Sheet3")
  do various stuff...etc.
End With

End Sub[/B][/I]

Howard

 

[zone709]

If you need to do stuff to the sheet named Data, you should be able to call a macro from any sheet to act upon the sheet named Data, regardless where sheet Data in in the sheet tab order.

If the macro to do the stuff to sheet Data is also required to do stuff to the activesheet, then that is okay also. The code will need to be specific to the sheet it is acting upon. There is seldom a need to actually SELECT the sheet to have the code effectively do its work on a given sheet.

And if you are going to loop through all the sheets (as it appears you do) and you want to exclude sheet Data from that loop, then that is possible also.

a non-working example...

[I][B]Sub aName_Whatever()
[/B]
[B]For each sheet in workbook

  If sht.Name <> Data then 
      sht.Cells.Replace What:="Data", Replacement:="'Data (2)'", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False  
  
  End If

Next

With Sheets("Sheet3")
  do various stuff...etc.
End With

End Sub[/B][/I]

Howard

ok thanks for your help will look at this.

 

[zone709]

Hi I tried something like this and a bunch of other stuff but get compile error.

Sub Sheets_Macro_Call()
  For Each Sheet In Workbook
    
      If sht.Name <> Data Then
        Call MacroTimeRetail
        
       If sht.Name <> Data(2) Then
        Call MacroTimeRetail2
        
   End If

End Sub
<strike></strike>

Am I close?

I also tried

Sheets(1).Name

but didn't work.

 

[zone709]

Hi I finally got it to work after a small thought of maybe this little thing will help. This is probably not the right way to do it, but it works when using your code. So I will stick with it.

Sub Sheets_Macro_Call()
  Dim ws As Worksheet
  
 Worksheets(1).Select         <--- I just added this. So if I am working on another sheet I can run macro. I hope you understood what I needed now thanks'
    
  With ws
    If ActiveSheet.Name = "Data" Then
        Call MacroTimeRetail
        
       ElseIf ActiveSheet.Name = "Data(2)" Then
        Call MacroTimeRetail2
        
    End If
  End With
   
End Sub