ExcelUser5005
New Member
- Joined
- Jan 23, 2019
- Messages
- 9
Hi
Im trying to create pivot table but getting error at "Set Mypivottable = " repeatedly. Searched lot could not find solution.
pls help
Im trying to create pivot table but getting error at "Set Mypivottable = " repeatedly. Searched lot could not find solution.
pls help
Code:
Sub Create_pivot()
Application.ScreenUpdating = True
Dim wb As Workbook
Dim count1, count2, count3 As Integer
Dim Rng1 As Range
Dim PvtFilterValue As String
Dim Sec1 As String
Dim Num, DestinationRow As Integer
Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim MyPivotTable As PivotTable
Dim mySourceData As String
Dim myDestinationRange As String
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim pf As PivotField
Dim PCache As PivotCache
Set wb = ActiveWorkbook
Sheet3.Activate
Set ws1 = ActiveSheet
Set ws2 = Sheet8
PvtFilterValue = "x"
ws1.Select
With ws1
.AutoFilterMode = False
End With
count1 = Application.WorksheetFunction.CountA(Range(Range("K1"), Range("K1").End(xlToRight)))
count2 = Application.WorksheetFunction.CountA(Range(Range("K1"), Range("K1").End(xlDown)))
Num = 1
DestinationRow = 3
myDestinationRange = ws2.Range("A" & DestinationRow).Address(ReferenceStyle:=xlR1C1)
myFirstRow = 1
myLastRow = count2
myFirstColumn = 1
myLastColumn = count1 + 10
With ws1.Cells
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
End With
ws2.Activate
Set MyPivotTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=ws1.Name & "!" & mySourceData, Version:=xlPivotTableVersion14) _
.CreatePivotTable(TableDestination:=ws2.Name & "!" & myDestinationRange, _
Tablename:="PivotTable" & Num, DefaultVersion:=xlPivotTableVersion14)
Sec1 = ws1.Range("K" & Num).Value
With ws2.PivotTables("PivotTable" & Num).PivotFields(Sec1)
.Orientation = xlRowField
.Position = 1
End With
ws2.PivotTables("PivotTable" & Num).PivotFields(Sec1).PivotFilters.Add Type _
:=xlCaptionContains, Value1:=PvtFilterValue
With ws2.PivotTables("PivotTable" & Num).PivotFields("Signal_Type")
.Orientation = xlRowField
.Position = 2
End With
With ws2.PivotTables("PivotTable" & Num)
.InGridDropZones = True
.ShowDrillIndicators = False
.RowAxisLayout xlTabularRow
End With
With ws2.PivotTables("PivotTable" & Num)
.ColumnGrand = False
.RowGrand = False
End With
ws2.PivotTables("PivotTable" & Num).PivotFields(Sec1).Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ws2.PivotTables("PivotTable" & Num).PivotFields("Signal_Type").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ws2.PivotTables("PivotTable" & Num).AddDataField ActiveSheet.PivotTables( _
"PivotTable" & Num).PivotFields("Cable OD"), "Sum of Cable OD", xlSum