none
Edit a Pivot Table data source by using VBA code RRS feed

  • Question

  • I have a Pivot table on Sheet "Pivot".  I want to update the Pivot Table for new data entered in Sheet "2011".  I will do this by using a Command Button , Macro, Worksheet_Change, etc.  The Pivot Table already exists so I do not need to create one.  I just want to use "Change Data Source" in macro form to add the newly entered data entered below the previous Pivot Table data.

    Max = Sheets("2011").Cells(Rows.Count, "F").End(xlUp).Row '200
    
     Sheets("Pivot").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
    
      PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    
      Sheets("2011").Range("B4:H" & Max).CurrentRegion _
    
      , Version:=xlPivotTableVersion14)
    The above code does not work. I do not want to create a new Pivot Table.  I just want to edit the current.  Could someone help me get this up and running.
    Thursday, March 10, 2011 7:53 AM

Answers

  • Hi

    Why do you think you are creating a new pivot table?  The code specifically identifies Sheets("Pivot").PivotTables("PivotTable1").

    I might suggest a more flexible way of defining the new range

    Sub Macro1()

    Range("A1").Select
    Selection.CurrentRegion.Select

    DataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
     
        ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
            Version:=xlPivotTableVersion14)
    End Sub

    This is based on the data being in A1 so if B4 is the top left of the data table then change the cell references and

    DataArea = "Sheet1!R4C2:R" & Selection.Rows.Count +3 & "C" & Selection.Columns.Count +1

    This way if the table changes in anyway your covered.


    G North MMI
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:22 AM
    Thursday, March 10, 2011 9:48 AM

All replies

  • Hi

    Why do you think you are creating a new pivot table?  The code specifically identifies Sheets("Pivot").PivotTables("PivotTable1").

    I might suggest a more flexible way of defining the new range

    Sub Macro1()

    Range("A1").Select
    Selection.CurrentRegion.Select

    DataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
     
        ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
            Version:=xlPivotTableVersion14)
    End Sub

    This is based on the data being in A1 so if B4 is the top left of the data table then change the cell references and

    DataArea = "Sheet1!R4C2:R" & Selection.Rows.Count +3 & "C" & Selection.Columns.Count +1

    This way if the table changes in anyway your covered.


    G North MMI
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:22 AM
    Thursday, March 10, 2011 9:48 AM
  • I might suggest a more flexible way of defining the new range

    Sub Macro1()

    Range("A1").Select
    Selection.CurrentRegion.Select

    DataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
     
        ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
            Version:=xlPivotTableVersion14)
    End Sub

    This is based on the data being in A1 so if B4 is the top left of the data table then change the cell references and

    DataArea = "Sheet1!R4C2:R" & Selection.Rows.Count +3 & "C" & Selection.Columns.Count +1

    This way if the table changes in anyway your covered.


    G North MMI

    Awesome, G North MMI!

    This is exactly what I needed. 

    Thank you.

    --Data Bender

    • Proposed as answer by mfaheem3 Friday, February 28, 2014 11:42 AM
    Wednesday, November 20, 2013 6:12 PM
  • What does Version 14 means from the code "Version:=xlPivotTableVersion14". Will it keep changing ?

    -Vidya

    Tuesday, March 1, 2016 9:39 AM