1 Attachment(s)
VBA Pivot Table creation looks different than manual creation
Hi Folks,
I have created a pivot table through VBA as seen in the attached picture. The following code is what I used to create it. What I wanted to know is why does the table show the different blank DROP sections in addition to the table data? Did I do something wrong in my code? The table works fine.
thanks,
marc
Attachment 16100
Code:
Sub MakePivotTable()
'this creates a new sheet for the pivot table. what if pivot table already exists?
Dim pvtTbl As PivotTable
Dim pvtTblCache As PivotCache
Dim pvtFld As PivotField
Dim pvtItem As PivotItem
Dim sh1 As Worksheet, sh9 As Worksheet 'sh3 As New Worksheet 'sh1 is data, sh3 is the pivot table
Dim rngToPivot As Range
Dim sPivotLoc As String 'where to place the pivot table on the new sheet
'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 sh3.PivotTables
' If MsgBox("Delete existing Pivot Table!", vbYesNo) = vbYes Then
' pvtTbl.TableRange2.Clear
' End If
'Next pvtTbl
Set sh1 = Sheet1 'Data
sh1.Activate
Range("A1").Select
Set rngToPivot = ActiveCell.CurrentRegion 'sets source data range
Set sh9 = Sheet9
'Set sh3 = ActiveWorkbook.Sheets.Add(Type:=xlWorksheet) 'creates a new sheet on which pivot table is built
sPivotLoc = sh9.name & "!R3C1"
'sPivotLoc = sh3.name & "!R3C1"
Application.ScreenUpdating = False
'create Pivot Cache and Pivot Table version Excel 2010
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngToPivot, Version:=xlPivotTableVersion14) _
.CreatePivotTable tabledestination:=sPivotLoc, tablename:="VBA_PT", defaultversion:=xlPivotTableVersion14
Set pvtTbl = sh9.PivotTables("VBA_PT")
'Set pvtTbl = sh3.PivotTables("VBA_PT")
pvtTbl.RowAxisLayout xlTabularRow 'display pivot table report in tabular form
pvtTbl.TableStyle2 = "PivotStyleLight17" 'change the stytle of the pivot table
pvtTbl.InGridDropZones = True 'view the pivot table in classic pivot table layout
pvtTbl.ColumnGrand = False 'hide grand totals in pivot table for columns
pvtTbl.RowGrand = False 'hide grand totals in pivot table for rows
pvtTbl.ManualUpdate = True 'turn off pivot table updating while creating
'create pivot fields for pivot table
With pvtTbl
Set pvtFld = .PivotFields("Quotation Number")
pvtFld.Orientation = xlRowField
pvtFld.Position = 1
pvtFld.LayoutForm = xlTabular
Set pvtFld = .PivotFields("Product Model")
pvtFld.Orientation = xlRowField
pvtFld.Position = 2
pvtFld.LayoutForm = xlTabular
Set pvtFld = .PivotFields("Parent Name")
pvtFld.Orientation = xlRowField
pvtFld.Position = 3
pvtFld.LayoutForm = xlTabular
Set pvtFld = .PivotFields("Config Option")
pvtFld.Orientation = xlRowField
pvtFld.Position = 4
pvtFld.LayoutForm = xlTabular
Set pvtFld = .PivotFields("row id")
pvtFld.Orientation = xlRowField
pvtFld.Position = 5
pvtFld.LayoutForm = xlTabular
'hide subtotals for all fields in the pivot table
For Each pvtFld In .PivotFields
' pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False 'sets the field that contains the active cell to not show Sum subtotals
Next pvtFld
End With
pvtTbl.ManualUpdate = False 'turn on pivot table updating
sh9.name = "VBA_PT"
'sh3.name = "VBA_PT"
Application.ScreenUpdating = True
End Sub
