Change Pivot Table to new range

[A A Ron]

I have been trying to tweak this for a long time, and I'm sure there is an easy fix for this, I just haven't figured it out.

My goal is to automatically update the Pivot Table based on the new data in the worksheet with Headings on row 5. Any help would be greatly appreciated. Thank You in advance.

Sub AdjustPivotDataRange()




Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String


'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = ActiveWorkbook.Worksheets("Completed Projects")
  Set Pivot_sht = ActiveWorkbook.Worksheets("Current Cycle Time BB.MBB")


'Enter in Pivot Table Name
  PivotName = "Pivot7"


'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A5")
  Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
  
  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)


'Make sure every column in data set has a heading and is not blank (error prevention)
  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If


'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(Pivot7).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
      
'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(Pivot7).RefreshTable






End Sub

 

[arthurbr]

Do you mean you are just adding or deleting rows from a range and the PT should update?
If so, select your range and then Insert Table - Use this Table as your data source
You can insert a small macro for updating when one or other event ( worksheet_activate or whatever you need) happens

[B]Private Sub Worksheet_Activate()[/B]

    Run "PivotMacro"

End Sub[B][B]Refresh a Single Pivot Table[/B][/B] [B]Sub PivotMacro()[/B]

Dim pt As PivotTable



    Set pt = ActiveSheet.PivotTables("MyPivot")

    pt.RefreshTable

[B]End Sub[/B]

 

[A A Ron]

Is there any way to do it without making the range a table though?

 

[A A Ron]

I found a solution...Thanks.