How can I get an Excel macro/module to run on a specific sheet?-CodePudding

Let's say I have 10 sheets in an Excel workbook and Sheet2 through Sheet10 are all unique in how they need to be formatted. Let's say I recorded 9 unique macros for each and I want a central location to house all the buttons (in Sheet1), one button for each recorded macro so that when the user goes to sheet 1 and clicks a button called "sheet 2" it will run the recorded macro for sheet 2 against sheet 2, even if the user is on the active sheet 1. Or if the user clicks the button called "sheet 10" it runs the recorded macro for sheet 10 against sheet 10, etc. Is this possible?

One of the comments mentioned to include one of the recorded macros, here is an oversimplified version of one of the macros.

Sub Sheet2()
'
' Sheet2 Macro
'

'
End Sub
Sub Sheet2Macro()
'
' Sheet2Macro Macro
'

'
    Range("A2:C2").Select
    Selection.AutoFill Destination:=Range("A2:C10"), Type:=xlFillDefault
    Range("A2:C10").Select
End Sub

I also found this bit of code online that I added at the top and it runs to success but somehow it only runs on the active sheet:

    Dim WkSheets As Variant, SheetName As Variant, ws As Worksheet
    
    '** SET The Sheet Names - MUST Reflect Each Sheet Name Exactly!
    WkSheets = Array("Sheet 2")
        
    For Each SheetName In WkSheets
    
        'MsgBox SheetName
        
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name = SheetName Then
                
                'Your Code Here

CodePudding user response：

If you are using 9 unique macros that you recorded, those will be set specifically for the sheets they are recorded from, and then you simply tie in the code to be called when the button for each specific one is clicked. As stated before it would be helpful to know if they were Form Controls or ActiveX Controls, but you would change the text in your button to say "Sheet 2 Button" for instance, and the code tied to that button would be something along the lines of

Private Sub Sheet2Button_Click()
    Call Sheet2Macro
End Sub

So from there when you click the button that says Sheet 2 Button, it will call Sheet2Macro and that macro was recorded on Sheet2 so will run for Sheet2 no matter where the button is stored. Hopefully that makes sense.