Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

Sub www()
Dim lr As Long: lr = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = 6 To 16
    If Cells(x, 2) <> "промежуточный итог" Then
        For y = 20 To lr
            For r = 3 To 15
                If Cells(x, 2) = Cells(y, 2) Then Cells(x, r) = Cells(x, r) + Cells(y, r)
            Next r
        Next y
    End If
Next x
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Public x
Private Sub Worksheet_Calculate()
    If [c1] <> x Then
        ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh
x = [c1]
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c1")) Is Nothing Then Exit Sub
ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh
End Sub