PDA

View Full Version : VBA Pivot Table creation looks different than manual creation



mfreem02
05-05-2016, 06:09 AM
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

16100


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

Bob Phillips
05-06-2016, 03:01 AM
Why have you posted this when you have already posted this http://www.vbaexpress.com/forum/showthread.php?55954-Test-Pivot-Table

mfreem02
05-10-2016, 09:02 AM
Because the other post was in the Test forum where we are supposed to go to test posting to forums before actually posting to forums. I wanted to test the post to be sure I understood how to attach pictures and format the coding before actually posting it to the Excel forum. I don't know why it is the one that received all the answers instead of this one. Anyway, someone answered the other one and it solved my question. I can mark this one solved if you like but you may want to connect the answers from the other one to it.