Nr of times a macro is run from a sheet

[jb007]

Hello everybody

I need to count how many times a macro is run from a specific worksheet.
For example if I am in sheet A and I run the macro 3 times, it ok to get the value 3, but if I move to sheet B and run it for the first time, I should get the value 1. Then if I move back and run from sheet A, value 4 should appear.

Using a static variable like the one from below that holds its value until the worbook is closed doesn't get the job done, because it counts every macro run, no matter from where.

Static a As Long
a = a + 1

 

[ZVI]

The similar idea about usage of sheet's name was in post #7 but with a lot of my typos in comments and without clear explanations.
I prefer the Mike's detailed suggestion anyway – nice code & description as usual!

 

[Rick Rothstein]

   ....
    ....
    recordSheet.Range("A:B").ClearContents
    For Each oneBook In Application.Workbooks
        For Each oneSheet In oneBook.Worksheets
            With recordSheet.Range("A65536").End(xlUp).Offset(1, 0)
                .Cells(1, 1).Value = "[" & oneBook.Name & "] " & oneSheet.Name
                [B][COLOR=#FF0000]On Error Resume Next[/COLOR][/B]
                .Cells(1, 2).Value = Evaluate(oneSheet.Names("MacroRunCount").RefersTo)
            End With
        Next oneSheet
    Next oneBook
    ....
    ....

Why not move the red highlighted code line to right before the first For..Each statement so that it is not continually being executed with each iteration of the loops?

 

[mikerickson]

^Good call.