Macro not giving correct output in runtime

[AkhileshPratap]

Hi All,

i am creating a pivot table and copying the TableRange2 value and pasting it to another sheet. then i change some data fields in pivot and again copying the new output TableRange2 and pasting it to that sheet. I repeat this process twice.

Problem is - In runtime, the macro copies the TableRange2 first output to all three places. while in debug mode i get all distinct values in three places which is as I expect.

Please help

Sub SummarizeData()
'
    Dim wsTrial As Worksheet
    Application.DisplayAlerts = False
    Err.Clear
    On Error Resume Next
    Set wsTrial = Sheets("Trail")
    If Err = 0 Then wsTrial.Delete
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Trail"
        
    Dim PvtTbl As PivotTable
    Dim wsData As Worksheet
    Dim DataRng As Range
    Dim PvtTblCache As PivotCache
    Dim wsPvtTbl As Worksheet
    Dim pvtFld As PivotField
    
    'determine the worksheet which contains the source data
    Set wsData = Worksheets("Associate Data")
    
    'determine the worksheet where the new PivotTable will be created
    Set wsPvtTbl = Worksheets("Trial")
    
    'delete all existing Pivot Tables in the worksheet
    'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
    For Each PvtTbl In wsPvtTbl.PivotTables
    'If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
    PvtTbl.TableRange2.Clear
    'End If
    Next PvtTbl
    
    'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.
    
    'set source data range:
    Set DataRng = wsData.Range("A5:BS" & wsData.Range("BS65535").End(xlUp).Row)
    
    'for creating a Pivot Cache (version excel 2007), use the PivotCaches.Create Method. When version is not specified, default version of the PivotTable will be xlPivotTableVersion12.
    
    'The PivotCache.CreatePivotTable method creates a PivotTable report based on a Pivot Cache. TableDestination is mandatory to specify in the method.
       
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DataRng, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Trail!R1:R1048576", TableName:= _
        "AssociateDataPivot", DefaultVersion:=xlPivotTableVersion12
            
    'add row, column and page (report filter) fields: For the week
    
    With ActiveSheet.PivotTables("AssociateDataPivot").PivotFields("Associate Id")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("AssociateDataPivot").PivotFields("Category")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("AssociateDataPivot").AddDataField ActiveSheet.PivotTables( _
        "AssociateDataPivot").PivotFields("FTW Entered Hrs"), "Sum of FTW Entered Hrs", xlSum
    
    ActiveSheet.PivotTables("AssociateDataPivot").CompactLayoutColumnHeader = ""
    ActiveSheet.PivotTables("AssociateDataPivot").DataPivotField.PivotItems( _
        "Sum of FTW Entered Hrs").Caption = "For the week"
    ActiveSheet.PivotTables("AssociateDataPivot").CompactLayoutRowHeader = "Associate Id"
    
    ActiveSheet.PivotTables("AssociateDataPivot").TableRange1.Select
    
    Selection.Copy
    
    Sheets("Summary").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    ' For the month
        
    ActiveWorkbook.Sheets("Trial").Select
    
    ActiveSheet.PivotTables("AssociateDataPivot").TableRange1.Clear
    
    ActiveSheet.PivotTables("AssociateDataPivot").PivotFields("For the week").Orientation _
        = xlHidden
     
    ActiveSheet.PivotTables("AssociateDataPivot").AddDataField ActiveSheet.PivotTables( _
        "AssociateDataPivot").PivotFields("FTM till this week entered Hrs"), "Sum of FTM till this week entered Hrs", xlSum
    
    Application.CutCopyMode = False
    
    ActiveSheet.PivotTables("AssociateDataPivot").DataPivotField.PivotItems( _
        "Sum of FTM till this week entered Hrs").Caption = "For the month"
    
    
    ActiveSheet.PivotTables("AssociateDataPivot").TableRange1.Select
    Selection.Copy
    
    Sheets("Summary").Select
    Range("J6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'add row, column and page (report filter) fields: For Till Date
        
    ActiveWorkbook.Sheets("Trial").Select
    ActiveSheet.PivotTables("AssociateDataPivot").TableRange1.Clear
    
    ActiveSheet.PivotTables("AssociateDataPivot").PivotFields("For the month").Orientation _
        = xlHidden
        
    ActiveSheet.PivotTables("AssociateDataPivot").AddDataField ActiveSheet.PivotTables( _
        "AssociateDataPivot").PivotFields("Till date  hrs"), "Sum of Till date  hrs", xlSum
    
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("AssociateDataPivot").DataPivotField.PivotItems( _
        "Sum of Till date  hrs").Caption = "Till Date"
    
    ActiveSheet.PivotTables("AssociateDataPivot").TableRange1.Select
    
    Selection.Copy
    
    Sheets("Summary").Select
    Range("S6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Trail").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("Summary").Select
 
End Sub