Answered by:
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.
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.SelectDataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
End SubThis 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
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.SelectDataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
End SubThis 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
-
I might suggest a more flexible way of defining the new range
Sub Macro1()
Range("A1").Select
Selection.CurrentRegion.SelectDataArea = "Sheet1!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
End SubThis 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 MMIAwesome, 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
-