title: How to update pivot data without clearing PivotTable Fields on Excel VBA?
body: <p>My current code seems to be updating the source data range, but it is clearing the fields and filters. Is there any way to avoid that? </p>
  
  <pre><code>sub update_pivot()
  
  'change data range for pivot
     Dim EndRow As Integer
     Dim DataRange As Range
     Dim NewRange As String
     Dim Data_Sheet As Worksheet
     Dim Pivot_Sheet As Worksheet
  
  
     Set Data_Sheet = Workbooks(myfile).Worksheets("Consolidation")
     Set Pivot_Sheet = Workbooks(myfile).Worksheets("Consolidation Pivot")
  
  
  ' find the last row in my data sheet
     Data_Sheet.Select    
     EndRow = Cells(Rows.Count, "A").End(xlUp).Row
  
     Set DataRange = Data_Sheet.Range("$A$1:$I" &amp; EndRow)
     NewRange = Data_Sheet.Name &amp; "!" &amp; DataRange.Address(ReferenceStyle:=xlR1C1)
  
     Pivot_Sheet.Select
     Pivot_Sheet.PivotTables("PivotTable6"). _
     ChangePivotCache ActiveWorkbook. _
     PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
  
  ' Ensure Pivot Table is Refreshed
     Pivot_Sheet.PivotTables("PivotTable6").RefreshTable
  
  End Sub
  </code></pre>