Excel 2007 Macro Recorded Pivot Table

I开发者_如何学运维 recorded a macro in excel 2007 in which I created a pivot table. When i tried to run the macro, I get "invalid procedure call or argument".

I looked at the debug window the "problem code" is:-

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C6:R15874C11", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Table!R1C1", TableName:="PivotTable7", _
DefaultVersion:=xlPivotTableVersion12

How can a recorded macro that do not work again? (I have cleared, removed and recreated the destination sheet and all sorts) and what is wrong with the macro itself ?

Have you tried breaking the code down to see more specifically where the problem lies?

For example,

Dim pvc As PivotCache
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                            "Data!R1C6:R15874C11", _
                                            Version:=xlPivotTableVersion12)
pvc.CreatePivotTable TableDestination:=Worksheets("Table").Range("A1"), _
                     TableName:="PivotTable7", DefaultVersion:=xlPivotTableVersion12

Also, perhaps try

Worksheets("Table").Range("A1")

instead of

"Table!R1C1"

Not sure if that will make any difference though...

Have you also checked:

There isn't a pivot table underneath where you are trying to create this one?

There are suitable headings for the pivot field names in row 1, columns 6 to 11?

I know you said you've recreated the sheet etcetera, but always worth checking there isn't a pivot table floating around... try:

Dim pvt As PivotTable
For Each pvt In Worksheets("Table").PivotTables
    Debug.Print pvt.Name
    Debug.Print pvt.TableRange2.Address
    pvt.ClearTable
    pvt.TableRange2.Clear
Next pvt

Other than that, I'm afraid I'm stumped.

Edit:

Sub ChangeSourceData()
    Worksheets("Table").PivotTables("PivotTable7").SourceData = "Data!R1C1:R25C6" 
End Sub

Make sure you have:

A sheet named Data

A sheet named Table

No other pivot tables named PivotTable7