Update Pivot Table using VBA (Change Data Source and Add New Data into Pivot)

If you have a data set in which a new column is added at a specified time interval then refreshing the pivot every time becomes monotonous.

Excel macro can be used to automate this process.

1. Changing the old data source and adding the new column into the data set.

2. Manipulating the table structure and and adding the new columns into 

the rows or columns or filters or values.

3. Refreshing the table structure with the new data.

Given below is the code to do the same.

1. Adding the new column in values section

Sub()


Sheet1.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw data!R3C1:R21C" & CStr(Sheet2.UsedRange.Columns.Count) _
        , Version:=xlPivotTableVersion14)
    
    Sheet1.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(fieldName), "Sum of " & fieldName, xlSum

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

    ActiveWorkbook.RefreshAll


The new field can also be added to the xlRowField, xlColumnField and xlPageField

Please find example file here.
https://drive.google.com/file/d/0B20WcznMWRTfMzBnd29JeG9LalE/view?usp=sharing