0

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
Edgar Nava
  • 21
  • 3
  • 4
    Yes, this is possible. If you recorded macros, make sure you read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your macros. You need to get rid of all `Select` and `ActiveSheet` statements in your code using this technique to get what you want. – Pᴇʜ Apr 26 '22 at 06:11
  • 1
    Yes, that is possible. Though -to be honest- that is usually not the kind question you should be asking here? What you could do is give the process of building the above a red-hot go by searching for examples on the internet. If you at some stage get stuck and cannot figure how to continue, you can ask a question here and people will be happy to help out. Good luck! – mtholen Apr 26 '22 at 06:12
  • Why don't you post one of those codes so you can conclude what has to be done by the way this code gets fixed by the community? – VBasic2008 Apr 26 '22 at 06:16
  • Thanks all, I'll chip away at this tomorrow, it's nearly midnight for me. – Edgar Nava Apr 26 '22 at 06:31
  • Please show a [mcve] or the real code you use. `'Your Code Here` is importand as we need to see what you put three. As exactly there is your issue located. – Pᴇʜ Apr 26 '22 at 07:52
  • It isn't clear what is the purpose of macro Sheet2(). Also it would be great to know what kind of buttons do you use: is it a Button (Form Control) or Command Button (ActiveX Control)? – Vitalizzare Apr 26 '22 at 12:58
  • @mtholen thanks for the input...maybe others post on this site without having tried to solve on their own but I certainly had given it a burning sizzling go but to no avail. In the end though, I figured it out with a little tinkering and a little help some folks here :) – Edgar Nava Apr 27 '22 at 02:41

1 Answers1

0

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.

DizturbdOne
  • 26
  • 2
  • As far as @EdgarNava had **recorded** macros, they are full of local addressing. So, IMO, it would be great to add `Thisworkbook.Worksheets("Sheet2").Activate` before calling `Sheet2Macro`. – Vitalizzare Apr 27 '22 at 08:25