Trying to activate this macro when E3 is double-clicked and only on sheet names with 3 characters
Hoping you guys can assist
Thank You
Code: Select all
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$E$3" Then
Dim wsh As Worksheet
Dim sFile As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
sFile = "Attrition!"
For Each wsh In Worksheets
If Len(wsh.Name) = 3 Then
With wsh.Range("G90:CD91")
.FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
& "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
.Value = .Value
End With
With wsh.Range("G131:CD132")
.FormulaR1C1 = "=Round((SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)+ " _
& "IF(R12C>EOMONTH(TODAY(),R3C5),SUMPRODUCT((Attrition!R3C2:R222C2=RC1)*(Attrition!R3C3:R222C3=RC2)*(Attrition!R3C1:R222C1=R1C4)*(Attrition!R3C4:R222C4=""Post Attrition"")*(Attrition!R2C5:R2C80=R12C),Attrition!R3C5:R222C80)))*R157C,0)"
.Value = .Value
End With
End If
Next wsh
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub