VBA Code to Update a Pivot's Data Source

Question

Please help!

In my workbook I have got an existing pivot table. It is on Sheet 4. The data souce for that pivot is on Sheet 3. I am trying to write a code (to be linked to a button located on Sheet 4) which will update the pivot's data source. My code so far is;

Sub Update_pivot_data()

Sheet3.Activate

Range("A1").Select

Selection.CurrentRegion.Select

DataArea = "Sheet3!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count

Sheet4.Activate

         ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _

         PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _

         Version:=xlPivotTableVersion14)

End Sub

The above selects the required region but the part to update the pivot is not working. Could you please suggest where I am going wrong? Thanks in advance.

Answer 1

Hi,

if you overwrite data in existing source range

everything is okay.

but,

if you add extra data (append data) below

unfortunately, in this occasion you can not update the pivot table.

Only if you convert the range to a table.

and then create a new pivot table.

here..

http://youtu.be/hDVSmyRhkkQ

xxxxxxxxxxxxxxxxxxx

here is a vba  macro

to refresh all pivot tbls in active sheet

and remove/delete old items

Sub PT_Refresh_RemoveOldItems()

'Feb 12, 2015

On Error Resume Next

Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

pt.PivotCache.Refresh

Next pt

End Sub