TableDestination: Pivot Table Macro

d33rhnter

New Member
Joined
Jun 28, 2013
Messages
2
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

d33rhnter

New Member
Joined
Jun 28, 2013
Messages
2
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,085
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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!

Code:
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.
 

Forum statistics

Threads
1,170,963
Messages
5,873,014
Members
432,955
Latest member
minhnhat2504

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top