Macro not giving correct output in runtime

AkhileshPratap

New Member
Joined
Apr 12, 2013
Messages
2
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


Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,196,516
Messages
6,015,664
Members
441,914
Latest member
VBAllTheThings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top