VBA Pivot Table creation looks different than manual creation

[mfreem02]

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

PivotTable.jpg

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]

Why have you posted this when you have already posted this http://www.vbaexpress.com/forum/show...st-Pivot-Table

[mfreem02]

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.