Macro to create Pivot Table Office '10

[amwilber]

Hello guys!

I am working to try and create a macro that will create a pivot table automatically without me having to go and create it every single time I download some data. I run a report every day, and pivot off of it, so its kind of a pain to do that every day. Anyway, I wrote some code to do this, and was going to assign it to a button, but when I click the button I am getting an error. The error I am getting is listed below, as well as the code.

Error Message:

Run-time error '5':
Invalid procedure call or argument


The code I am using that is highlighted as erroring out:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Testing Data!R1C1:R2038C24", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion10

Can someone give me a hand with this, can't figure out why this code is erroring...

Thank you in advance, I appreciate the help!

~Andrew

 

[ziad alsayed]

Dear amwilber

to create a pivot by using VBA, you need to do the following

1- define last row in your data
2- define last column in you data
3- define the Pivot range
4 define the pivot cache
5 create the pivot table from the pivot cash.

below is a sample , you can amend to your need.

Sub CreatePivot()
    
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Set WSD = Worksheets("PivotTable")
        
    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
        
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)
    
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")
    
    ' Turn off updating while building the table
    PT.ManualUpdate = True
    
    ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Region", "Customer"), _
        ColumnFields:="Product"
    
    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    'Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
    
    WSD.Activate
    Range("J2").Select
End Sub

i you need any other assistance do not hesitate to post your question again

hope this can help.

 

[jvanbonn]

Re: Macro to create Pivot Table - Excel 2007

With the following line of code...

 ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Region", "Customer"), _
        ColumnFields:="Product"

Can I use an array variable to add fields like so?

PT.AddFields RowFields:=HeaderArray

such that HeaderArray is...

Dim HeaderArray() As String

 

[Lidsavr]

Your code looks like it was created by the macro editor. If I am correct, you need to add apostrophes before and after the worksheet names in two locations:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"[COLOR="Red"]'[/COLOR]Testing Data[COLOR="red"]'[/COLOR]!R1C1:R2038C24", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="[COLOR="red"]'[/COLOR]Sheet2[COLOR="red"]'[/COLOR]!R3C1", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion10

See the apostrophes in RED above.

This will make the code you provided work without received an 'Error 5'.

Charles

 

[noviceprogrammer]

I'm having somewhat of a similar problem!
I created a macro that recorded my actions for creating the pivottable but when I go to run it again I get
Run-time error '1004':
Application- defined or object- defined error

And when i click debug its refering to the first piece of code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Entire_Defects_List!R1C1:R992C15", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet9!R3C1", TableName:="PivotTable4" _
, DefaultVersion:=xlPivotTableVersion14

What can I do to fix this?

Many thanks for the help!