VBA - Which sheet called macro, then do something

Question

Is there another way besides Activesheet.Name to get which sheet is calling a macro?

I currently have code where you press a button then it runs a simulation program, then once it runs the program it goes to another sheet in the workbook and pastes information.

This works if I run the macro one at a time on each sheet, but I want to be able to run them all at the same time.

Example: I have a input sheet "Run1" that contains the button, then once the simulation is done it does something in a sheet called "Results1"

I have created duplicate sheets of "Run1" and "Results1" and incremented the number at the end

If ActiveSheet.Name = "Run1" Then
    t = 1
End If
If ActiveSheet.Name = "Run2" Then
    t = 2
End If
If ActiveSheet.Name = "Run3" Then
    t = 3
End If

If ActiveSheet.Name = "Run4" Then
    t = 4
End If

If ActiveSheet.Name = "Run5" Then
    t = 5
End If

If ActiveSheet.Name = "Run6" Then
    t = 6
End If

Worksheets("Results" & t).Activate

For Each A In ActiveSheet.ChartObjects("Chart 9").Chart.SeriesCollection
    A.Delete
Next A

If I click all the buttons at the same time, it throws an error, because its trying to go to a Results sheet based on which Run sheet the user is currently on. And since the simulation program is running, the code doesn't complete synchronously, excel can't find the Results sheet that the first button macro is calling because it isn't active.

First a suggestion, you edit the above code into a single 'If-Else If- End If' tree. Looks neater and will perform better. Now one possible alternative would be to call the macro with a parameter that is a string identifier for the sheet. — shash, Apr 25 '18 at 20:41

Tim Williams · Accepted Answer · 2018-04-25T21:00:23.787

First read this link and adjust your code to remove all unneeded select/activate actions: How to avoid using Select in Excel VBA

Then you can do something like this:

If ActiveSheet.Name Like "Run*" Then
    With Worksheets("Results" & Replace(ActiveSheet.Name, "Run", ""))
        '? all your charts are called "Chart 9"?
        For Each A In .ChartObjects("Chart 9").Chart.SeriesCollection
            A.Delete
        Next A
        .Activate
    End With
End If

VBA - Which sheet called macro, then do something

1 Answers1