Macro Runtime Errors When Creating Pivot Tables

[kkunert]

Hi, I am neophyte in the world of macros, but I badly need their productivity value to help my small business.

I have single Excel 2007 workbooks with five worksheets. On each of these worksheets are large amounts of data relating to M&A transactions. I can have as many as 5,000 rows and 47 columns. The worksheets correspond to a single quarter in a yearm so 1Q00, 2Q00, 3Q00, 4Q00 and a full year worksheet FY00.

A pivot table can be used on the data in each worksheet to break down the count and aggregate value of M&A activity in a given quarter of full year based on an industry label of sector label.

The pivot tables are great, but I have many reports to do over the period of 2000-2010 for the U.S., Europe, Asia, North America, and Global. I am hoping to speed up the pivot table process by using macros. However, I am encountering constant errors when I try to record and run a macro. I create tables out of my data and then record exactly how I want my pivot table to look, then I stop the recording.

But when I test the macro, it invariably pops us an error of runtime 5, runtime 1004, a range problem or an inability to get PivotFields property of PivotTable Class.

This is debug highlight:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion12


This is the VB code:

Sub indpiv()
'
' indpiv Macro
'
' Keyboard Shortcut: Ctrl+l
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields( _
"Announced/Initial Filing Date (Including Bids and Letters of Intent)"), _
"Count of Announced/Initial Filing Date (Including Bids and Letters of Intent)" _
, xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Transaction Value ($USDmm, Historical rate)") _
, "Sum of Total Transaction Value ($USDmm, Historical rate)", xlSum
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Industry [Target/Issuer]")
.Orientation = xlColumnField
.Position = 1
End With
End Sub


Does anyone see an error that stands out to them?

Any help would be greatly appreciated.

Kurt

 

[AlphaFrog]

Try something like this...

Sub indpiv()
'
' indpiv Macro
'
' Keyboard Shortcut: Ctrl+l
'
    Dim rngDATA As Range, wsPVT As Worksheet, ptNew As PivotTable
    

    Set rngDATA = Sheets("Sheet1").Range("A1").CurrentRegion
        
    Set wsPVT = Sheets.Add(After:=Sheets(Sheets.Count))
    
    Set ptNew = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rngDATA) _
                              .CreatePivotTable(TableDestination:=wsPVT.Range("A3"))
    
    With ptNew
    
        .AddDataField .PivotFields("Announced/Initial Filing Date (Including Bids and Letters of Intent)"), _
                                   "Count of Announced/Initial Filing Date (Including Bids and Letters of Intent)", xlCount
                                   
        .AddDataField .PivotFields("Total Transaction Value ($USDmm, Historical rate)"), _
                                   "Sum of Total Transaction Value ($USDmm, Historical rate)", xlSum
                                   
        With .DataPivotField
            .Orientation = xlRowField
            .Position = 1
        End With
        
'        With .PivotFields("Primary Industry [Target/Issuer]")
'            .Orientation = xlColumnField
'            .Position = 1
'        End With
        
    End With
    
End Sub

I couldn't figure out what you wanted to do with...
With .PivotFields("Primary Industry [Target/Issuer]")
...so I commented it out.

Your macro doesn't create a "Primary Industry [Target/Issuer]" pivot table data field.

 

[kkunert]

AlphaFrog,

Thanks very much for the reply. I tried what you suggested. Unfortunately, when I run it on a table, it creates a runtime error 9 - subscript out of range.

 

[AlphaFrog]

What line is highlighted when you debug it?

 

[kkunert]

Hi, this is the highlighted line of code when debugging:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet1!R3C1", TableName:="PivotTable12", DefaultVersion:= _
xlPivotTableVersion12

Your help is much appreciated.

 

[kkunert]

Thank you , AlphaFrog, for considering the problem. I found a solution that works for me.

http://support.microsoft.com/kb/940166

This was the answer I needed.

 

[gouldy74]

I had the same issue but the link in the post aboce sorted it.

Thanks Guys.