locked
changing source data for pivot using vba RRS feed

  • Question

  • Hello all Please help with the below code every time i try to save excel throws error

    Sub ChangeDataSource()

    With ActiveSheet.Shapes(Application.Caller).ControlFormat
        ActiveSheet.PivotTables("PivotTable2").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable3").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable4").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable5").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable6").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable7").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable8").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable9").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable10").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable11").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
        ActiveSheet.PivotTables("PivotTable12").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)

    End With

    End Sub


    Saturday, November 19, 2016 4:34 PM

All replies