TableDestination: Pivot Table Macro

[d33rhnter]

Hey guys, I'm new to the forum but over the past week found a lot of useful data that has lead me to my first problem I am unable to resolve using other's posts.

I've created a pivot table that is placed on an existing worksheet using a dynamic named table for the range. I've been able to create a macro that works fine when the table is placed in a new worksheet by emptying the table destination and removing a few lines, but not so much when I try and place the table in an existing worksheet. This leads me to believe my issue lies somewhere in the table destination area. Below I've copied and pasted the script with the bold area in question. I've also pasted below that what I've tried to do to "fix" it.

Sub PT_Field_Summary()
'
' PT_Field_Summary Macro
'


'
ActiveWorkbook.PivotCaches.<wbr>Create(SourceType:=xlDatabase, SourceData:= _
"Dynamic_Field_Summary", Version:=<wbr>xlPivotTableVersion10).<wbr>CreatePivotTable _
TableDestination:="Field Summary!R1C16", TableName:="PivotTable7", _
DefaultVersion:=<wbr>xlPivotTableVersion10
Sheets("Field Summary").Select
Cells(1, 16).Select
ActiveWorkbook.<wbr>ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("<wbr>PivotTable7").PivotFields("<wbr>Enterprise")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("<wbr>PivotTable7").PivotFields("<wbr>Field ")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("<wbr>PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("<wbr>Planted Acres"), "Sum of Planted Acres", xlSum
ActiveSheet.PivotTables("<wbr>PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("<wbr>Harvested Acres"), "Sum of Harvested Acres", xlSum
With ActiveSheet.PivotTables("<wbr>PivotTable7").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("<wbr>PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("<wbr>lbs "), "Sum of lbs ", xlSum
ActiveWorkbook.<wbr>ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-3
End Sub



Using some google searches I was able to begin a "fix" listed below. I swore I had it working at the office earlier, but no avail now. I've changed the font on the area I played with.


ActiveWorkbook.PivotCaches.<wbr>Create(SourceType:=xlDatabase, SourceData:= _
"Dynamic_Field_Summary", Version:=<wbr>xlPivotTableVersion10).<wbr>CreatePivotTable _
TableDestination:=Worksheets("<wbr>Field Summary!").Range("P1"), TableName:="PivotTable7", _
DefaultVersion:=<wbr>xlPivotTableVersion10



When I save and attempt the macro from here I get a Run Time Error 9, subscript out of range.

I learned what an excel macro and pivot table was on Monday, making a lot of headway thanks to your community!

 

[Marcelo Branco]

Which version of Excel are you using?

*Maybe* the problem is in Version:=<WBR>xlPivotTableVersion10.

For Excel 2007 you should use xlPivotTableVersion12 and for Excel 2010 xlPivotTableVersion14

See this
XlPivotTableVersionList Enumeration (Microsoft.Office.Interop.Excel)

M.

 

[d33rhnter]

Which version of Excel are you using?

*Maybe* the problem is in Version:=<wbr>xlPivotTableVersion10.

For Excel 2007 you should use xlPivotTableVersion12 and for Excel 2010 xlPivotTableVersion14

See this
XlPivotTableVersionList Enumeration (Microsoft.Office.Interop.Excel)

M.

Thanks for your input Marcelo, I am running excel 2010 so that table version is definitely different.

I altered "Version:=<wbr style="font-family: Arial;">xlPivotTableVersion10" to "Version:=<wbr style="font-family: Arial;">xlPivotTableVersion14" but am still getting the same error message. Also, the working script that places the pivot table on a new sheet is also running on "Version:=<wbr style="font-family: Arial;">xlPivotTableVersion10". I've listed this working script below in case it will help at all.

Sub PT_New_Sheet()
'
' PT_New_Sheet Macro
'


'
ActiveWorkbook.PivotCaches.<wbr>Create(SourceType:=xlDatabase, SourceData:= _
"Dynamic_Field_Summary", Version:=<wbr>xlPivotTableVersion10).<wbr>CreatePivotTable _
TableDestination:="", TableName:="PivotTable6", DefaultVersion _
:=xlPivotTableVersion10
With ActiveSheet.PivotTables("<wbr>PivotTable6").PivotFields("<wbr>Enterprise")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("<wbr>PivotTable6").PivotFields("<wbr>Field ")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("<wbr>PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("<wbr>Planted Acres"), "Sum of Planted Acres", xlSum
ActiveSheet.PivotTables("<wbr>PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("<wbr>Harvested Acres"), "Sum of Harvested Acres", xlSum
With ActiveSheet.PivotTables("<wbr>PivotTable6").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("<wbr>PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("<wbr>lbs "), "Sum of lbs ", xlSum
ActiveWorkbook.<wbr>ShowPivotTableFieldList = False
End Sub

Thanks again for your help.

 

[Marcelo Branco]

Looking at your code and the Pivot Fields i tried adapt a code that i had used some time ago.

Not sure if it fits exactly with your data.

A shot in the dark! (but hope it can help)

Here we go!

Sub newPVT()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    'Create the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=Range("Dynamic_Field_Summary"))
        
    'Select the destination sheet
    Sheets("Field Summary").Select
    
    'Create the Pivot table
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
        TableDestination:=Range("P1"), TableName:="Pivot1")
        
    ActiveWorkbook.ShowPivotTableFieldList = True
    
    'Adding fields
    With PT
        With .PivotFields("Enterprise")
            .Orientation = xlColumnField
            .Position = 1
        End With

        With .PivotFields("Field")
            .Orientation = xlRowField
            .Position = 1
        End With
    
        With .PivotFields("Planted Acres")
            .Orientation = xlDataField
            .Position = 1
            .Caption = " Planted Acres"
            .Function = xlSum
        End With
        
        With .PivotFields("Harvested Acres")
            .Orientation = xlDataField
            .Position = 2
            .Caption = " Harvested Acres"
            .Function = xlSum
        End With
        
        With .PivotFields("lbs")
            .Orientation = xlDataField
            .Position = 3
            .Caption = " lbs"
            .Function = xlSum
        End With
        
        'Adjusting some settings
        .RowGrand = False
        .DisplayFieldCaptions = False
        .HasAutoFormat = False
        
        'Improving the layout
        .TableStyle2 = "PivotStyleMedium9"
        .ShowTableStyleRowStripes = True
        .ShowTableStyleColumnStripes = True
        
    End With
    
    With ActiveSheet
        'Adjusting columns width
        .Columns("P:V").ColumnWidth = 16
        .Range("Q2:V2").HorizontalAlignment = xlCenter
    End With
    
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Good luck!

M.