问通过VBA更新Excel透视表区域获取的行数太多

EN

Sub UpdateTable()
 Dim Data_Sheet As Worksheet
 Dim Pivot_Sheet As Worksheet
 Dim StartPoint As Range
 Dim DataRange As Range
 Dim PivotName As String
 Dim PivotTables(3) As String
 Dim Table As Integer
 Dim NewRange As String
 Dim LastCol As Long
 Dim lastRow As Long

 'MsgBox Prompt:="Running UpdateTable"

 
 'Set Pivot Table & Source Worksheet
 Set Data_Sheet = ThisWorkbook.Worksheets("owssvr")
 Set Pivot_Sheet = ThisWorkbook.Worksheets("Summary")
 
 PivotTables(0) = "pvtOverOpen"
 PivotTables(1) = "pvtOverAgeBracket"
 PivotTables(2) = "pvtOverCreate"
 PivotTables(3) = "pvtOverClosed"
 'Loop through each pivot table and update it
 For Table = 0 To 3 Step 1
 'Enter in Pivot Table Name
 PivotName = PivotTables(Table)
 'Defining Staring Point & Dynamic Range
 Data_Sheet.Activate
'**********************************************************************************
 Set StartPoint = Data_Sheet.Range("A1")
 Set DataRange = Data_Sheet.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
 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)
'***********************************************************************************
 MsgBox Prompt:=NewRange
 'Ensure Pivot Table is Refreshed
 Pivot_Sheet.PivotTables(PivotName).RefreshTable
 'End of For Loop
 Next Table
End Sub