Edit a Pivot Table data source by using VBA code

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.

Answer 1

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

Answer 2

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

Answer 3

What does Version 14 means from the code "Version:=xlPivotTableVersion14". Will it keep changing ?

-Vidya