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