pivot table should update via vba

[ichbinich]

i searched online or vba macro that my pivot table source data should update automatically
and i found this

Sub UpdatePivotTableRange()


Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long


'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("sheet1")
Set Pivot_Sheet = ThisWorkbook.Worksheets("sheet1")


'Enter in Pivot Table Name
PivotName = "PivotTable1"
'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)


'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)


'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable


'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."


End Sub


now first i saved it to this workbook and it worked fine
but than i saved it the personal workbook because i want to use it by a lot of workbook
and it comes up
run-time error '9':
Subscript out of range

and it high lite this line
Set Data_Sheet = ThisWorkbook.Worksheets("sheet1")

any help?

 

[ichbinich]

problem solved