how can I shorten this code

[SamS]

I have to check a number of pivot tables to ensure that the Data fields are "Sum" and not "Count". I have the following code for 1 such pivot table but feel that it can be shortened, any advice would be appreciated with Thanks in advance.

Worksheets("First_Pivot").Select

If Left(Range("B10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of Mth Act").Function _
= xlSum
End If

If Left(Range("C10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of Mth Bud").Function _
= xlSum
End If

If Left(Range("D10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of Mth Var").Function _
= xlSum
End If

If Left(Range("E10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of YTD Act").Function _
= xlSum
End If

If Left(Range("F10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of YTD Bud").Function _
= xlSum
End If

If Left(Range("G10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of YTD Var").Function _
= xlSum
End If

If Left(Range("H10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of FY Fcast").Function _
= xlSum
End If

If Left(Range("I10").Value, 5) = "Count" Then
ActiveSheet.PivotTables("Finances").PivotFields("Count of FY Bud").Function _
= xlSum
End If

 

[AlphaFrog]

This loops through all the items in the Data field and changes the Count data fields to Sum data fields.

Select any cell in the Pivot Table 1st, then run the macro.

Sub PivotTable_Convert_CountFields_to_SumFields()

    Dim strActivePT As String
    Dim PF As PivotField
    Dim PI As PivotItem

    On Error Resume Next
        strActivePT = ActiveCell.PivotTable.Name
    On Error GoTo 0
    
    If strActivePT <> vbNullString Then
        With ActiveSheet.PivotTables(strActivePT)
            .ManualUpdate = True
            For Each PI In .PivotFields("Data").PivotItems
                Set PF = .PivotFields(PI.Name)
                If PF.Function = xlCount Then PF.Function = xlSum
            Next PI
            .ManualUpdate = False
        End With
        MsgBox "All 'Count' data fields have been converted to 'Sum' data fields.", vbInformation, "Conversion Complete"
    Else
        MsgBox "First select a cell in the Pivot Table you want to check.", vbExclamation, "Pivot Table Not Selected"
    End If
    
End Sub

 

[SamS]

Super, that will save an awful lot of coding, thanks.