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]

This may help. Pops the message with the sheet name, where you would have your code instead, to do your work when you activate the sheet.

Howard

Option Explicit

Private Sub Worksheet_Activate()

MsgBox ActiveSheet.Name

End Sub

 

[zone709]

This may help. Pops the message with the sheet name, where you would have your code instead, to do your work when you activate the sheet.

Howard

Option Explicit

Private Sub Worksheet_Activate()

MsgBox ActiveSheet.Name

End Sub

ok i'm basically just trying to see if i can run a macro probably small code to look at the activename sheet name tab let's say it's called "Data" Then choose either macro to run which would be Macrodata () in this case

 

[Tetra201]

... 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 ()...

Try placing the following sub into ThisWorkbook code section:

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

 

[L. Howard]

Does this work for you, copy to a standard module. Macro Sheets_Macro_Call() run on either sheet named Data or Data2 calls the macro for that sheet. If run on any sheet other than those two, response is nada.

The message box from the two called macros represents the code you will have in its stead.

Howard

Option Explicit

Sub Sheets_Macro_Call()
  Dim ws As Worksheet
    
  With ws
    If ActiveSheet.Name = "Data" Then
        Call Data
       ElseIf ActiveSheet.Name = "Data2" Then
        Call Data2
    End If
  End With
   
End Sub

Sub Data()
 MsgBox "Sheet name is " & """" & _
         ActiveSheet.Name & """" & vbCr & _
        "Data macro was called."
End Sub

Sub Data2()
MsgBox "Sheet name is " & """" & _
        ActiveSheet.Name & """" & vbCr & _
       "Data2 macro was called."
End Sub

 

[zone709]

Hi I I am trying to get this to go. I think I am close now, but it breaks at this point Sub MacroTimeRetail().

I probably have things in the wrong place. if you can take a look thanks.

Option Explicit
Sub Sheets_Macro_Call()
  Dim ws As Worksheet
    
  With ws
    If ActiveSheet.Name = "Data" Then
        Call Data
       ElseIf ActiveSheet.Name = "Data2" Then
        Call Data2
    End If
  End With
   
End Sub


Sub MacroTimeRetail()
 MsgBox "Sheet name is " & """" & _
         ActiveSheet.Name & """" & vbCr & _
        "Data macro was called."
End Sub


Sub MacroTimeRetail2()
MsgBox "Sheet name is " & """" & _
        ActiveSheet.Name & """" & vbCr & _
       "Data2 macro was called."
End Sub

Sub MacroTimeRetail() 'Adding Timesheet' '<--- breaks here   Ambiguous name detected'
 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

 

[L. Howard]

You have two macros with the same name, not allowed for obvious reasons. Delete or rename the test macros I supplied.

Change the sheet names shown in RED to the sheets you want each Call to run.

Howard

Sub Sheets_Macro_Call()
  Dim ws As Worksheet
    
  With ws
    If ActiveSheet.Name = "[COLOR=#FF0000]One_Worksheet[/COLOR]" Then
        Call MacroTimeRetail
        
       ElseIf ActiveSheet.Name = "[COLOR=#FF0000]Other_Worksheet[/COLOR]" Then
        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]

Boom works awesome Thanks again this helps not choosing the wrong run for a sheet I am doing.

Cheers

 

[L. Howard]

Okay great, glad it works for you.

Tetra201 in Post #4 shows a good, perhaps much better way, with the Application.Run "Macro" & Sheets(1).Name method.
Slightly modifies in the code below.

Try it out on a new test workbook with five worksheets named, Sheet1, Sheet2, Sheet3, Sheet4, HorseFeathers.
(The weird name is to demo the sheets can have any name)

Where you would run the macro Any_Sheet_Call from any of the five sheets.

Howard

All this in a standard module.

Option Explicit

Sub Any_Sheet_Call()
  Application.Run "Mac" & ActiveSheet.Name
End Sub

Sub MacSheet1()
  MsgBox "MacSheet1 code"
  [A1] = ActiveSheet.Name
End Sub

Sub MacSheet2()
  MsgBox "MacSheet2 code"
  [B2] = ActiveSheet.Name
End Sub

Sub MacSheet3()
  MsgBox "MacSheet3 code"
  [C3] = ActiveSheet.Name
End Sub

Sub MacSheet4()
  MsgBox "MacSheet4 code"
  [D4] = ActiveSheet.Name
End Sub

Sub MacHorseFeathers()
  MsgBox "MacHorseFeathers code"
  [A1:F1] = ActiveSheet.Name
End Sub

 

[zone709]

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.