How do I increase a number by one every time a macro run

[ghrek]

Hi
I have the following macro but im trying to make a number in cell D8 increase by one every time the clear sheet macro is run. Any ideas? I think that I have cell D8 clearing in the macro, Can anyone confirm yes or no it clears as obviously I dont want it to.

Range( _
        "D34:D35,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101,C5:D7,H11:H22" _
        ).Select
    Range("H22").Activate
    ActiveWindow.SmallScroll Down:=20
    Range("B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101,C5:D7,H11:H22") _
        .Select
    Range("H11").Activate
    ActiveWindow.SmallScroll Down:=-40
    Range("C5:D7,H11:H22").Select
    Range("H11").Activate
    ActiveWindow.SmallScroll Down:=24
    Range("C5:D7,H11:H22,D34:D35,B46:H47").Select
    Range("B46").Activate
    ActiveWindow.SmallScroll Down:=20
    Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56").Select
    Range("G56").Activate
    ActiveWindow.SmallScroll Down:=12
    Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71").Select
    Range("C60").Activate
    ActiveWindow.SmallScroll Down:=8
    Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71,G77:H77").Select
    Range("G77").Activate
    ActiveWindow.SmallScroll Down:=8
    Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88").Select
    Range("C81").Activate
    ActiveWindow.SmallScroll Down:=12
    Range( _
        "C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,C94:C101,G93:H93" _
        ).Select
    Range("G93").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-100
    ActiveWorkbook.Save
End Sub

 

[GWteB]

Your macro contains a lot of unnecessary code. The same worksheet ranges are selected multiple times, while once is sufficient.
Furthermore, it is almost never necessary to select worksheet ranges, so your code could look like the following.

Sub ghrek_01()
    
    ActiveSheet.Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,C94:C101,G93:H93").ClearContents
    ActiveWorkbook.Save
End Sub

Finally: with the code above it's more clear that the code leaves the contents of cell D8 intact.
To increase its value, combined with the above code:

Sub ghrek_02()
    
    With ActiveSheet
        .Range("C5:D7,H11:H22,D34:D35,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,C94:C101,G93:H93").ClearContents
        .Range("D8").Value = .Range("D8").Value + 1
        .Parent.Save
    End With
End Sub

 

[Toadstool]

Hi GHRek,

I see you already have a full answer but just let me confirm D8 isn't changed by your macro and to work you'll need D8 to initially be empty or contain a number.

My code to add was:

TheCounter = Range("D8").Value
TheCounter = TheCounter + 1
Range("D8").Value = TheCounter

 

[ghrek]

Big thanks

 

[GWteB]

Glad to help.