jeudi 20 juillet 2017

Pivot table creation in for loop

Facing problem with dynamic creation of pivot table in for loop, Below code is working fine, but randomly stuck after creating 30 tables.

Array in function contains 80 variables based on each variable each pivot will be created.

in the below loop, code randamly stops at this point

objTable1 = Sheets("Execution Sheet").PivotTableWizard(TableDestination:=ws.Cells(3, i)) enter code here

and objtable becomes NUll

Sub CreatePivot(valueSlaArray1() As String)

    Dim objTable As PivotTable, objField As PivotField, objTable1 As PivotTable, objField1 As PivotField
    Dim i As Integer, j As Integer
    i = 1
    j = 7
    Dim ws As Worksheet
    Dim tempcolname As String

    ActiveWorkbook.Sheets("Sheet_Output").Select
    Set ws = Sheets.Add
    ws.Name = "Summary"
    Dim col As String
    For Each Item In valueSlaArray1
     ' On Error Resume Next


    Set objTable1 = Sheets("Execution Sheet").PivotTableWizard(TableDestination:=ws.Cells(3, i))


    Set objField = objTable.PivotFields("Compare_" & Item)
    objField.Orientation = xlRowField

    'objField.Orientation = xlColumnField
   ' Set objField = objTable.PivotFields("Compare_" & Item)
   ' objField.Orientation = xlDataField
   ' objField.Function = xlCount
    i = i + 3

Next Item


Sheets("Summary").Cells(1, "A") = "Report Date"
    Sheets("Summary").Cells(1, "B") = Now()
    Sheets("Summary").Columns.AutoFit

End Sub




Aucun commentaire:

Enregistrer un commentaire