Excel 2007 Pivot Table creation through VBA

[luanub]

I am trying to create a pivot table through a VBA function. I'm using Excel 2007. I have tried several different methods and can not get Excel to create the table. Unfortunately I am not getting any error messages and the code runs fine. Its just after it is done there is no table, the sheet that I am trying to build it on remains blank.

Here are a couple of the functions that I have tried, I have also tried the wizard and recording the function as a macro and neither seems to work. Any idea's on what is going wrong?

1st code

Function createPivot()
    Dim WSD As Worksheet
    Dim wsData As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Hourly Report")
    Set wsData = Worksheets("Department_Summary")

'Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT

    Set PRange = wsData.Range("A2:R200")

    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("A1"), TableName:="PivotTable1")

    PT.ManualUpdate = True

    PT.AddFields RowFields:="Rack Type", ColumnFields:="Op Seq"

    With PT.PivotFields("Queue Qty")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With

'Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
End Function

2nd Code

    Dim wsData As Worksheet
    Dim wsPvtTbl As Worksheet
    Dim rngData As Range
    
    Set wsData = Worksheets("Department_Summary")
    Set wsPvtTbl = Worksheets("Hourly Report")
    Set rngData = wsData.Range("A2:R200")
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
        Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:= _
        "myChart", DefaultVersion:=xlPivotTableVersion12
    
    With ActiveSheet.PivotTables("myChart").PivotFields("Op Seq")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("myChart").PivotFields("Rack Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("myChart").AddDataField ActiveSheet.PivotTables( _
        "myChart").PivotFields("Queue Qty"), "Sum of Queue", xlSum

    ActiveSheet.PivotTables("myChart").PivotFields("Queue Qty").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("myChart").PivotFields("Rack Type").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("myChart").PivotFields("Op Seq").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    
    With ActiveSheet.PivotTables("myChart")
        .ColumnGrand = False
        .RowGrand = False
    End With
    ActiveSheet.PivotTables("myChart").RowAxisLayout xlOutlineRow

Ultimately I would like to get all the options in the 2nd code to work but at this point will settle for anything.

 

[Andrew Poulsom]

How are you calling the function?

 

[luanub]

How are you calling the function?

The function is being called from an ActiveX button.

Private Sub CommandButton1_Click()
    createPivot
End Sub

I have also tried just entering the code as a macro and running it that way. I get the same results either way.

 

[Andrew Poulsom]

Your code worked fine for me in Excel 2007. Do you have On Error Resume Next anywhere in your code? Are there headings in row 2 on worksheet "Department_Summary"?

 

[luanub]

Your code worked fine for me in Excel 2007. Do you have On Error Resume Next anywhere in your code? Are there headings in row 2 on worksheet "Department_Summary"?

Yes there is On Error Resume Next, and yes there are headings on row 2.

I'm guessing this is due to the On Error? I will go in and try to remove what I can and see if I can get it to work.

Thank you for your help, it's very much appreciated.


EDIT:

So that definately changed something. Now using either set of code I get a RunTime Error 13 Type Mismatch on the following lines.

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
        Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:= _
        "myChart", DefaultVersion:=xlPivotTableVersion12